Unanswered

Power Automate to Excel add row to a table date/time format get changed in Excel

(0) Share

Report

Posted on by Subramaniyam

Hello,

I am using Power Automate to convert Power BI data to excel format. After exporting to excel format my date format gets changed, so for days 01 to 12 excel does a custom formatting, whereas for other days the values of the cell are same as the original data.

For e.g.:- In below Arrival Image the date format is correct for 18th April to 29th April, but the format gets changed when the date starts for May and this behavior is till 12 Days of month (i.e. 1st to 12th of Month). This is so weird, as the end user will get lost by seeing the data.

I have used powerfx function formatDateTime()

Categories:

General topics

I have the same question (0)

All responses (8)

Answers (0)

LLinx 99 on at

Like (0)

Report
Copy link

Link copied!

Hi!

This probably is a date format related error. The reason it only does this behavior until the 12th of the month, is because it can only recognize a valid date in mm-dd-yyyy format until the 12th, after that it cannot convert it to a date.
You should probably check what localization is used to calculate the dates, it probably does not match the one from your Excel file.
If the goal is to have the data visible in the correct way within Excel, try changing the Date locale settings in Excel:

Was this reply helpful? Yes No
Garethterb1 131 on at

Like (0)

Report
Copy link

Link copied!

Hi.
When populating dates into excel it is normally best to convert the date into the excel date number eg 02 May 2024 = 45414 you can do this by converting your date to ticks and then converting tick to the excel date number. Your formula should look something like this if I remember correctly, int(div(sub(ticks({YourDateHere}),ticks('1899-12-30')),864000000000))

Was this reply helpful? Yes No
Chriddle 8,525 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

I strongly recommend only using ISO 8601 formatted date strings with PA connectors.

Was this reply helpful? Yes No
Subramaniyam 6 on at

Like (0)

Report
Copy link

Link copied!

Whatever i need to do is in power automate, i can't change settings of the excel file at each user who will view the file. Also it doesn't work

Was this reply helpful? Yes No
Subramaniyam 6 on at

Like (0)

Report
Copy link

Link copied!

I have tried to set the Date format as ISO 8601 in add row in a table . Still gives weird output. Microsoft Support needs to come up with a prompt solution for this scenario, as my client who are viewing data are not happy.

Also i have checked many answers in the community there is no correct answer to fix this issue. I need both date and time in the format dd-MM-yy hh:mm:ss

Was this reply helpful? Yes No
Chriddle 8,525 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

I have tried to set the Date format as ISO 8601 in add row in a table . Still gives weird output.

Of course the string in your "Current item" has to be formatted accordingly as well

Microsoft Support needs to come up with a prompt solution for this scenario

There is a suitable solution and it has already been described: Set the display format of the Excel column accordingly.

Microsoft Support needs to come up with a prompt solution for this scenario, as my client who are viewing data are not happy.

There may be a misunderstanding of how Excel (and similar software in general) works.

The only way to have your "date" formatted like this without changing Excel's display settings is to add it as a string (to do so add a quote at the beginning).

The disadvantage is that it is no longer a date in Excel (and e.g. not sortable)

Was this reply helpful? Yes No
Garethterb1 131 on at

Like (0)

Report
Copy link

Link copied!

The best solution, as I have already mention is to insert the date as its number into excel. The excel sheet will the respect the format of the cell.

int(div(sub(ticks(utcNow()),ticks('1899-12-30')),864000000000))

Was this reply helpful? Yes No
Subramaniyam 6 on at

Like (0)

Report
Copy link

Link copied!

I have used convert to csv table (Action) in Power Automate and then created an csv file, instead of .xlsx and it serves my purpose.

Also when i open it with excel and apply filter i can see the date appears correct

Power Automate Flow

Thanks!

Was this reply helpful? Yes No