Unanswered

Desktop Flow interacting with Excel Date format

(1) Share

Report

Posted on by Amohammed1503

Hi All,

I am an RPA dev and have created a bot that does a simple task where an excel file is downloaded from the web and the file is then opened and edited. One of the key issues I have been having with this bot is its ability to split data. One of the requirements of the bot is to split a column that has both time and date in it. ie 02/08/2021 10:00:00. What I have done is have the bot split the data by adding a new column and then having it do it through excel(Text to columns). The only issue with this is the fact that once the time has been removed, the formatting of the date cell remains and has a timestamp in it. To remove this, I have the bot format the column to Date> dd/mm/yyyy. Now it is important that the date format remains in as dd/mm/yyyy but it seems that every time the bot touches the dates columns, excel is reading the dates as mm/dd/yyyy.

I have tried to read the date off excel and change the format and put it back in but excel changes the dates back to the American standard. This is only happening to the date columns with which the bot changes or interacts.

I am unsure how to resolve this error and would love some help with the issue 🙂

Categories:

Power Automate Desktop

I have the same question (0)

All responses (3)

Answers (0)

MichaelAnnis 5,727 Moderator on at

Like (1)

Report

I am not sure of all the steps that are involved, or what the bot is writing to Excel, but here are some ideas based on what I read:

First, if I was given a list of dates & times in column A, I would do the following for the breakout:
In column B, have =TEXT(A1,"dd/mm/yyyy").
In column C, have =TEXT(A1,"hh:mm:ss").
Select Columns B & C, copy, paste values.

Now for PAD. Check your variables and see what it is reading or getting ready to write. However they look is what it should be writing. PAD has a similar feature for converting dates/times to text as Excel does.

The "m"s are case sensitive. Lower case is minutes and uppercase is months, so pay attention when using this PAD feature.

Best of luck.

Was this reply helpful? Yes No
amohammed 2 on at

Like (0)

Report

Found a solution actually! I had the exact same issue.

I had the excel open and read the date's values and store them in a list. Then close and open the excel and made it non-visible and replaced it with a standard date like 30/07/2021. Closed the excel again and open the excel again and made it non-visible and replaced all the 30/07/2021 with the data values read and stored in the list.

Seemed to do the trick. I think when PAD opens and makes changes to any dates in excel whilst its visible, the formatting is messed up but if you open the excel while its not visible and make the changes. It's fine.

Hope this helps for anyone else that has this issue!

Was this reply helpful? Yes No
roniercassio 12 on at

Like (0)

Report

I have the same problem. I'm trying to do a simple automation.
1st Read from a .csv file that contains a list of dates in dd/mm/yyy format.
2nd Open Excel
3rd copy the csv information into an active excel spreadsheet.
However, when you copy it to Excel, the dates change to MM/DD/YYY.

Was this reply helpful? Yes No