DatePicker to text Patching problem

(0) Share

Report

Posted on by Englishweb

371

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?

Categories:

Building Power Apps

I have the same question (0)

All responses (11)

Answers (1)

Verified answer

mdevaney 29,989 Moderator on at

Like (2)

Report

@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."

Was this reply helpful? Yes No
Englishweb 371 on at

Like (0)

Report

Hello @mdevaney
Thanks for the super quick reply.
Couldn't I just reformat the existing excel date column to text format?

Was this reply helpful? Yes No
mdevaney 29,989 Moderator on at

Like (0)

Report
@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.

Dates displayed in Excel would look like this:

---
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."

Was this reply helpful? Yes No
Englishweb 371 on at

Like (0)

Report

Sorry, this didn't work for me.
Could it be because the excel spreadsheet is in French so jj/mm/aaaa?

Was this reply helpful? Yes No
mdevaney 29,989 Moderator on at

Like (0)

Report

@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?

Was this reply helpful? Yes No
Paul_C Microsoft Employee on at

Like (0)

Report
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

Was this reply helpful? Yes No
Englishweb 371 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
mdevaney 29,989 Moderator on at

Like (0)

Report
@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."

Was this reply helpful? Yes No
Englishweb 371 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Englishweb 371 on at

Like (0)

Report

Thanks @mdevaney and @Paul_C for your help.
I have now converted the datepicker to text, which is patched to an Excel 'text ' format column as you suggested.
NOT refreshing the datasource in the app after reformatting the column was the source of the Patching problem.
Easy to overlook - lesson learned!

Was this reply helpful? Yes No