cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mollygwatson
Frequent Visitor

Day and month are switched when Power Automate writes to Excel

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:

mollygwatson_0-1662104339607.png

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:

mollygwatson_1-1662104442571.pngmollygwatson_0-1662106643761.png

 

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
v-liwei-msft
Community Support
Community Support

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.

vliweimsft_0-1662456492526.jpeg

 

Best Regards,

Levi

 

View solution in original post

5 REPLIES 5
v-liwei-msft
Community Support
Community Support

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:

vliweimsft_0-1662349544336.jpeg

 

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

vliweimsft_1-1662349567874.pngvliweimsft_2-1662349581436.png

 

When I set the column to be a text column:

vliweimsft_3-1662349590743.pngvliweimsft_4-1662349600131.png

 

 

Best Regards,

Levi

 

mollygwatson
Frequent Visitor

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
Community Support
Community Support

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.

vliweimsft_0-1662456492526.jpeg

 

Best Regards,

Levi

 

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
Community Support
Community Support

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

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (5,622)