DatePicker to text Patching problem

[Englishweb]

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?

[mdevaney · Accepted solution]

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

[Englishweb]

Hello @mdevaney 

Thanks for the super quick reply.

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

[mdevaney]

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

[Englishweb]

Sorry, this didn't work for me.

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

[mdevaney]

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

[Paul_C]

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 

[Englishweb]

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.

[mdevaney]

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

[Englishweb]

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.