Patch date picker value in a gallery to excel

[Englishweb]

Hello

I am using Excel as the data source for a GALLERY (not a form)

How do I patch a date using the date picker control back to an Excel cell?

Many thanks in advance for any suggestions!

[Englishweb]

I am using Excel as the data source for a GALLERY (not a form)

How do I patch a date using the date picker control back to an Excel cell?

Many thanks in advance for any suggestions!

[RusselThomas · Accepted solution]

Hi @Englishweb ,

I assume you already have a column to hold the date (let's call it MyDateColumn) in your source (let's call it MyExcelSpreadsheet) and a datepicker control inside the gallery? 

1. If not - first sort out the spreadsheet and make sure it's got the column you want and that you can see the column in PowerApps.
2. Then add the datepicker control inside the gallery and set it's Default: property to ThisItem.DateColumn
3. Then decide if you want to patch whenever the date is changed, or using a button. 

If you want a button, add it to the gallery row (so the button appears in each row of the gallery).  If you want a single button outside the gallery to patch multiple changes - that's going to be a very different post.

To make things easier, let's assume you just want to update the row any time the date is changed.

Set the OnChange: property of the datepicker control to 

Patch(MyExcelSpreadsheet, ThisItem, {MyDateColumn: DatePicker.Selected.Date})

For a row button, it would be the same formula but applied to the OnSelect: property.

Hope this helps,

RT

[Englishweb]

Hello RT

Thank you so much for your quick reply.

I'm getting an error message: MyDateColumn does not match the expected type 'DateTime'.

Do I need to format the Excel column as a date or leave it as General?

Sorry if the question is obvious!!

Thanks

[RusselThomas]

Hi @Englishweb ,

For Excel I'm not sure how well data types carry through to PowerApps - but you can just make it text using the Text() function if the source column is Text - so;

Patch(MyExcelSpreadsheet, ThisItem, {MyDateColumn: Text(DatePicker.Selected.Date, "dd/mm/yyyy")})

You can decide on the format using dd mm yyyy in whichever order you want, or for easier conversion back into date format inside PowerApps use one of the built in formats (like ShortDate) and use the region code to format - so;

Patch(MyExcelSpreadsheet, ThisItem, {MyDateColumn: Text(DatePicker.Selected.Date, ShortDate, "en-US")})

Different region codes will put the month, day or year in different places, so play with US, GB

Some reading for reference:

• https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-text
• https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/show-text-dates-times

Hope this helps,

RT