Day and month are switched when Power Automate writes to Excel

[mollygwatson]

This is an issue I've experienced for a while, where Power Automate will switch the day and month of a date when updating a row in Excel. I want my dates to appear in the dd/MM/yyyy format, but they are swapped to US MM/dd/yyyy.

This only happens when switching the day and month will result in a valid date e.g. 1st September 2022 should produce 01/09/2022 but instead shows 09/01/2022. However, 31st August 2022 should and does show 31/08/2022.

I have tried using the formatDateTime expression with different format strings and using the locale:

formatDateTime(utcNow(),'dd/MM/yyyy')

formatDateTime(utcNow(),'d','en-GB')

However, both result in the same error which leads me to believe the issue lies with the Excel action.

I'm currently building a flow which processes the date using this expression:

formatDateTime(triggerOutputs()?['body/receivedDateTime'],'d','en-GB')

The inputs for the Update a Row Excel action are able to format the date correctly:

The outputs show the date in the Excel Serial number format which, when copied and pasted into Excel converts the date to MM/dd/yyyy format:

I have tried changing the Advanced Settings of the action to use the ISO 8601 format but this doesn't seem to do anything, and the outputs appear the same.

Does anyone have any ideas on how to resolve this? Thanks!

[v-liwei-msft]

Hi @mollygwatson ,

This problem comes from Excel's settings for fields of date type. 

Your setup in flow is perfect! 

You can select all the data in the date column in Excel, set its type to text type, and the problem will be solved. 

I have made a test for your reference:

1.This is my flow:

When I don't set the column to be a text column: 

When I set the column to be a text column:

Best Regards,

Levi

[mollygwatson]

Hi @v-liwei-msft ,

Thanks so much for your answer - glad to know I've set my flow up properly!

I wanted to check if this is the only solution as I've already spotted 2 potential issues:

1. Any dates already in Excel get converted to serial numbers when you change the column to general or text format - not the end of the world, but I want to apply this change to a flow that affects a sheet with ~200 rows already populated
2. I lose the ability to filter by dates if I convert to text as this doesn't get sorted by month and year, just static dates - again, not the end of the world, but quite inconvenient

This might be caused by the way I'm formatting my dates in Power Automate, converting them to string. Do you know if there are any other potential solutions to keep using the date functions?

Thanks again!

Molly

[v-liwei-msft · Accepted solution]

Hi @mollygwatson 

Please excuse my late reply, I'm thinking about how to make your problem better. 

After modifying it to Text type and changing it back to Date type after the flow runs. 

Please pay attention to modify the display format of the date column in Excel.

Best Regards,

Levi

[mollygwatson]

Hi @v-liwei-msft,

I've tried running the flow with the Excel columns set to text and converting the data back to dates, this seems to work. If this is the only workaround then we can convert to dates if we need to, however I'd appreciate some exploration into other options as I think there should be an easier way to write dates in local format without having to keep converting types.

Thanks for your help on this!

Molly

[v-liwei-msft]

Hi @mollygwatson 

I'm sorry I didn't think of a simpler solution. 

Using Excel as a data source is not a good choice (weird issues always arise). 

Apologies again.

Best Regards,

Levi