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

DatePicker to text Patching problem

Hello

I have converted a datepicker date to text in a label called Datelabel. in a Gallery: 

Text(DatePicker.SelectedDate,"[$-en-GB]dd/mm/yyyy")    

This displays the date correctly, however,

When I try to patch the label Datelabel.Text  to the Excel datasource I get an error does not match expected DateTime. Found type 'Text' (I've tried patching the datapicker date to Excel but I always get the American format)

Why won't it patch a .text value here?

1 ACCEPTED SOLUTION

Accepted Solutions
mdevaney
Super User
Super User

@Englishweb 

The column you are patching to holds Dates so PowerApps will not allow you to update it with a Text value.  You may only patch the SelectedDate to that column.  The TEXT function cannot be used.

 

Would it be OK to create another column called DateText and patch to that instead?

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

11 REPLIES 11
mdevaney
Super User
Super User

@Englishweb 

The column you are patching to holds Dates so PowerApps will not allow you to update it with a Text value.  You may only patch the SelectedDate to that column.  The TEXT function cannot be used.

 

Would it be OK to create another column called DateText and patch to that instead?

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

Hello @mdevaney 

Thanks for the super quick reply.

Couldn't I just reformat the existing excel date column to text format?

@Englishweb 

You could do that but having the date stored in a date data type is very useful.  It allows you access to all of the DATE functions in PowerApps.

 

In PowerApps:

To display the proper date formatting you would use the code you already have

Text(ThisItem.SelectedDate,"[$-en-GB]dd/mm/yyyy")  

 

In Excel:

You can leave the column as a Date type and change the format to your custom setting like this.  It will still be a Date and it will display the way you want.

Untitled.png

 

Dates displayed in Excel would look like this:

Untitled2.png

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Sorry, this didn't work for me.

Could it be because the excel spreadsheet is in French so jj/mm/aaaa?

@Englishweb 

Yes, the French formatting could be causing your problem.  Unfortunately, I have not experienced working with a spreadsheet using custom French date formats.


Any possibility to convert the spreadsheet to English in some way?

 

Apologies if I'm missing something, but you can convert the text to a French-formatted date time and patch with that, I believe:

DateTimeValue(MyTime, "fr-FR")

For more information: 

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-datevalue-timevalue 

- Paul C.
Software Engineer on PowerApps

Hello @Paul_C 

Thanks for your reply. Its not the datepicker I'm having a problem with, its what Excel does with the date format when it is viewed in the app.

I can save the datepicker.selecteddate to a text label control in the correct format (dd/mm/yyyy) but I'm having problems patching this back to Excel.

@Englishweb 

Is it possible you could just PATCH your Excel sheet by referencing the DatePicker instead of the label?

 

Patch(
    your_excel_name,
    ID=your_record_id,
    {your_date_column: DatePicker.SelectedDate}
);

 

 

The label could just be used for display purposes and ignored for the PATCH.

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Hello @mdevaney 

That's exactly what I am doing at the moment but the stored date becomes 'corrupted' in Excel and so an incorrect date is sent to the recipient. It would be great if I could patch the label text to Excel as 'pure' text, rather than the datepicker SelectedDate.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,154)