cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Englishweb
Post Patron
Post Patron

Patch date picker value in a gallery to excel

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!

1 ACCEPTED SOLUTION

Accepted Solutions
RusselThomas
Microsoft
Microsoft

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

View solution in original post

3 REPLIES 3
RusselThomas
Microsoft
Microsoft

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

View solution in original post

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

 

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:

Hope this helps,

RT

 

 

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (1,467)