Excel date formats changes after manipulation

(0) Share

Report

Posted on by garthh

138

Starting with a csv file that has many columns, the desktop flow deletes unwanted columns

Two date format columns exist, both are in the correct DMY format in the original spreadsheet

After the Flow deletes the unwanted columns some dates change

Single digit day dates become MDY

Double day digit dates remain in DMY

See column AO in the attachment. It becomes column C after the Flow has deleted unwanted columns - Date format changes.gif

Note duplicate rows were also deleted by the Flow after the unwanted columns were deleted.

When doing the Flow Actions manually via mouse/keyboard the date formats do not change

Ideas to resolve?

Categories:

Power Automate Desktop

I have the same question (0)

All responses (9)

Answers (1)

Sort by

Nived_Nambiar 18,138 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!
Hi @garthh

I think you can use vbscripting to convert the format of dates present in C column, see below

Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True Set objWorkbook = objExcel.Workbooks.Open("C:\Path\To\Your\Workbook.xlsx") ' Change the path to your workbook Set objWorksheet = objWorkbook.Sheets("Sheet1") ' Change the sheet name as needed Set objRange = objWorksheet.Range("C2:C" & objWorksheet.Cells(objWorksheet.Rows.Count, "A").End(-4162).Row) ' -4162 corresponds to xlUp in VBA For Each objCell In objRange If IsDate(objCell.Value) Then objCell.NumberFormat = "dd/MM/yyyy" End If Next objWorkbook.Save objWorkbook.Close objExcel.Quit Set objRange = Nothing Set objWorksheet = Nothing Set objWorkbook = Nothing Set objExcel = Nothing

For running the above script, use Run VBScript action in power automate desktop

This would help to convert dates to dd/MM/yyyy format.

Thanks & Regards,
Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

Was this reply helpful? Yes No
Verified answer

Agnius Bartninkas Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

It's Excel that does it for you and it depends on the regional settings on your machine and the Excel app. It sounds like it actually expects dates to be in MDY, but leaves those it cannot convert to a "valid" date in MDY as DMY (because there cannot be a 13th month).

You can likely fix this by adjusting the regional settings on your machine, as well as in Excel to work with dates in DMY.

Alternatively, use scripting, as suggested by @Nived_Nambiar to fix the format after Excel messes it up. But that will likely not work if Excel is pre-formatting your dates, because it probably does not even interpret the dates that it doesn't convert at all. It assumes those are strings and will not format them anyway.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

Was this reply helpful? Yes No
garthh 138 on at

Like (0)

Report
Copy link

Link copied!

Agnius, thank you
I'll pass on to our IT to look at regional settings in Excel.

Was this reply helpful? Yes No
garthh 138 on at

Like (0)

Report
Copy link

Link copied!

Navid, thank you. At what stage in the Flow would you run the script?
After the columns have been deleted?

Was this reply helpful? Yes No
garthh 138 on at

Like (0)

Report
Copy link

Link copied!

Navid and Agnius
I have another issue. I'm finding it impossible at present to get the recorder to cut and paste columns in spreadsheets.
There is no Fn key that I can find in the Send Keys Action so Fn+t Fn+e shortcuts can't operate
Thoughts?

Was this reply helpful? Yes No
garthh 138 on at

Like (0)

Report
Copy link

Link copied!

Nived
I'm a novice as you know but presume the VB script ran as the Flow did not error
The dates did not change after the columns were deleted

Was this reply helpful? Yes No
garthh 138 on at

Like (0)

Report
Copy link

Link copied!

Apology - I had forgotten!
The Excel Actions of Copy Cells, Insert Column and Paste Cells works well
Shortcuts aren't neded

Was this reply helpful? Yes No
Agnius Bartninkas Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

As mentioned, the reason for the formats is the regional settings. VBscript that Nived suggested will only work if Excel actually interprets the dates as actual dates correctly. Since it doesn't, it cannot format them in a custom format either. You need to fix your regional settings for this to work.

-------------------------------------------------------------------------

If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

Was this reply helpful? Yes No
garthh 138 on at

Like (1)

Report
Copy link

Link copied!

Agnius, thank you
Our IT found the issue in 365 setup. The setting MM/DD/YYYY
All works correctly now

Was this reply helpful? Yes No