cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Englishweb
Level 8

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
Super User
Super User

Re: Patch date picker value in a gallery to excel

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
Super User
Super User

Re: Patch date picker value in a gallery to excel

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

Englishweb
Level 8

Re: Patch date picker value in a gallery to excel

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

 

Super User
Super User

Re: Patch date picker value in a gallery to excel

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
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,502)