Date change when writing to excel xlsx

(0) Share

Report

Posted on by kriho

Hi Community

Date change when writing to excel file.?

Outcome

2 dates have changed.?

Greetings

Kristian Holm

Developer

Categories:

Power Automate Desktop

I have the same question (0)

All responses (13)

Answers (0)

Sort by

momlo 1,527 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

This is OS/Excel regional settings conflict issue not PAD issue.
It is actually row 1 and 2 recognized as date.
Third row is not recognized by Excel as date but as txt
It seems your OS uses MM-dd-yyyy and you are passing dd-MM-yyyy.

Try reformatting your variables to match OS regional settings, otherwise you will need to format the Excel cells before you paste the values.

Was this reply helpful? Yes No
ThomasFonn 273 on at

Like (0)

Report
Copy link

Link copied!

Hello @kriho

I'll just post a dirty workaround.
Instead of using the "Write to Excel worksheet"-action, you could instead use the "Activate Cell in Excel Worksheet"-action and then use the "Send keys"-action (don't forget to add a {Return} at the end).
If you get the same problem as before, you may have to look at the cell format for those cells in particular, as momlo suggested.

Was this reply helpful? Yes No
kriho 44 on at

Like (0)

Report
Copy link

Link copied!

Hi momlo
My OS uses dd-MM-yyyy

Was this reply helpful? Yes No
ViliBit 127 on at

Like (1)

Report
Copy link

Link copied!

You can use "Convert datetime to text" action and save dates as text on excel, avoiding auto format by excel.

Was this reply helpful? Yes No
momlo 1,527 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Looks ok, I woudl only check in Excel if there is no setting overwriting default date.
Did you try reformatting your variables to see if this solves the issue?

Was this reply helpful? Yes No
kriho 44 on at

Like (0)

Report
Copy link

Link copied!

Hi Momlo

No difference in saving from date to text and then writing to excel

Was this reply helpful? Yes No
momlo 1,527 Super User 2024 Season 1 on at

Like (3)

Report
Copy link

Link copied!

I took more time and tested on my machine:

1. Regional settings matach yours: short date = dd.MM.yyyy

2. I assume based on your code, this is format you want to get in excel:

3. I recreated your code

4. And I got... the same result as you. But! We thought it is row 3 put incorrectly and rows 1 and 2 ok, but this is exactly opposite if we use formulas to grab parts of a date:

5. I did copy/paste the values from notepad and voila! Values are correct in Excel. So definitely issue between PAD and Excel

6. I have then reformatted variables in pad from your 'dd-MM-yyyy' to 'MM-dd-yyyy' and this time - correct results in Excel.

So to wrap up:
When pasting value from PAD (yours and mine) - values are assumed by Excel as 'MM-dd-yyyy' as suggested initially.
I try to avoid using pasting dates into Excel - afaik it is nightmare with those formats conflicts, so for example I often put time stamps as text.

Was this reply helpful? Yes No
kriho 44 on at

Like (0)

Report
Copy link

Link copied!

Hi momlo thanks for the test
I am a little concerned about PAD assume that date is MM-dd-yyyy. Because what if the date already is MM-dd-yyyy will it not change it to dd-MM-yyyy.?
When writing the date it should be that what you see is what you get and no change in format.

An example when you write a datatable using write to Excel worksheet and the dateformat is dd-MM-yyyy
is very convenient first to change the dateformat to MM-dd-yyyy in the datatable before using the action write to Excel worksheet.?
Datatable

Writing to excel using datatable

date has changed.
The OS is dd-MM-yyyy
To me that should be an error. It is not logic to change the date format everytime you write to excel.?

Greetings
Kristian Holm
RPA Developer

Was this reply helpful? Yes No
MarcThomas 8 on at

Like (0)

Report
Copy link

Link copied!

Hello @kriho

I am also a danish RPA developer who has just run into the same issue as you had.

Did you ever find a solution to this?

Was this reply helpful? Yes No
kriho 44 on at

Like (1)

Report
Copy link

Link copied!

Hi Marc try to set timezone to system when you getd
power automate desktop is set as default to American date
The American date is month/day/year
you can also Change date to text and then put a empty space in front of the date
greetings kristian

Was this reply helpful? Yes No