Answered

Day and month swap when sent to Excel

(0) Share

Report

Posted on by mdesloges

Hi,

I have a simple flow that looks for new file in Sharepoint and record the file name with the date of creation in an excel file.

All works except the date of creation which return the day and month swapped.

In Power Automate, I can see in the log the Date being displayed like 2024-04-08T06:06:58Z. So April the 8th.

In the "Add line to Excel" block, I did set a file DATE like : formatDateTime(triggerBody()?['Created'],'dd/MM/yyyy')

In the log I see DATE as "08/04/2024", 8th of april (which is my defaut location setting as I am in Europe).

But when I open the excel file, the 8th of April becomes the 4th of August !

Is there a setting somewhere or what do I do wrong in the blocks ?

I search though but could not make it to work.

Thanks

All responses (4)

Answers (1)

Verified answer

mdesloges 13 on at

Like (1)

Report

Re: Day and month swap when sent to Excel

Hi @v-yetonggu-msft , @Chriddle , I made this change in the Add line to Excel block : formatDateTime(triggerBody()?['Created'],'yyyy-MM-dd')
And the result is :
Created Date of file in the Sharepoint Block :2024-04-09T07:35:01Z - ALL GOOD
Date in the Add line to Excel block :2024-04-09 - ALL GOOD
Date in Excel file : 09/04/2024 : 9th of April 2024 !!! ALL GOOD
Thanks to both for the tip!
Solved
v-yetonggu-msft on at

Like (0)

Report
Re: Day and month swap when sent to Excel

Hi @mdesloges ，

My last reply was wrong, I will be more rigorous in my future replies.

The standard date format of US is not 'dd/MM/yyyy', but 'MM/dd/yyyy'.

So you should not use the expression formatDateTime(triggerBody()?[' Created'],'dd/MM/yyyy'), please use formatDateTime(triggerBody()?[' Created'],'MM/dd/yyyy').

formatDateTime(triggerBody()?[' Created'],'MM/dd/yyyy')

Or you can use the 'yyyy-MM-dd' format @Chriddle said.

Best Regards,

Sunshine Gu
Chriddle 7,652 Super User 2025 Season 1 on at

Like (0)

Report

Re: Day and month swap when sent to Excel

You switched month and date yourself 😉

Use formatDateTime(triggerBody()?['Created'],'yyyy-MM-dd')

or just

triggerBody()?['Created'] and set the display format in Excel accordingly.
mdesloges 13 on at

Like (0)

Report

Re: Day and month swap when sent to Excel

@v-yetonggu-msft
it does become August 8. When I filter all the april days, I do not see the 8th of april. But I see august 8th. So excel definetly see it and recognize it as August not april. See below the example snapshots.

Thanks for support
Block : Add line in Excel
Line in Exce file
Filter of the Excel column