Trying to patch a UK date to Excel

[jed76]

Hi

Yes I know Excel is a bad data source(!) but on this occasion I need to write a simple date column to a sheet.

I have a Date formatted column and whenever I patch a date to it, it shows in this kind of format:

2023-09-21T14:04:09.996Z

I just want it to show as 21/09/2023.

I have tried DateValue followed by , "dd/mm/yyyy" but then the cell is completely blank??

Can anyone suggest the format I can use please in the patch statement?

Many thanks. 

[JohnM86]

To format the date correctly in your patch statement, you can use the Text function in PowerApps to convert the date to the desired format. Here's an example of how you can modify your patch statement to format the date as dd/mm/yyyy:

Patch(
ExcelTable,
{ ID: 1 }, // replace with the record ID you want to update
{ DateColumn: Text(SelectedDate, "dd/mm/yyyy") }
)

In this example, ExcelTable is the name of your Excel table, ID is the name of the primary key column in your table, DateColumn is the name of the date column you want to update, and SelectedDate is the date value you want to patch.

The Text function takes two arguments: the first is the value you want to convert to text (in this case, SelectedDate), and the second is the format you want to use. In the example above, we're using the "dd/mm/yyyy" format.

Note that if the date value in SelectedDate is null or blank, the Text function will return an empty string. If this is the case, you may want to handle it separately to avoid patching an empty string to your Excel table.

[jed76]

Hi

Many thanks but this didn’t work either as the column in Excel is formatted as date instead of text. I got round it in the end by changing the format of the column to TEXT. This created another issue though as the sheet also collects data from an online form too so I had to incorporate a flow to take the form data and reformat in the flow using a formatdatetime expression to “dd/mm/yyyy” and this then worked correctly with the text column of the Excel too.

Date formats cause be so much grief in Powerapps!!!!

[JohnM86]

To update the "DateColumn" with the correct format, the serial date value needs to be converted to an Excel date format using the "Text" function in PowerApps. The "Text" function takes two arguments: the first is the value to be formatted, and the second is the format string to use. In this case, the format string needs to be "[$-en-US]mm/dd/yyyy;;@", which is the standard date format for Excel in the US locale.

Here's the updated expression using the Patch function with the correct date format:

Patch( ExcelTable, { ID: 1 }, // Specify the record ID to update { DateColumn: Text(DateValue(SelectedDate), "[$-en-US]mm/dd/yyyy;;@") } )

This expression should update the specified record in "ExcelTable" with the new value for "DateColumn" in the correct Excel date format.