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

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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (2,796)