Wrong date format when using 'Read Excel file'

RPA Automation Troubleshooting
1

I need to compare data from two different Excel files in order to create an array of booleans. One of the columns that need to be compared is ‘Date’. For the comparison to return the correct result, the columns from both files need to be in the same format, but that is not what is happening…

One file has the format DD/MM/YYYY (my preferred format), whereas the other one has the format YYYY-MM-DDT00:00:00. Both files are downloaded and saved by the bot. I don’t know why the formats between them are different.

Can anyone help me? All I need is the format in both files to be the same, but I don’t know how to do that.

2

Hi @cris-dsc! Thank you for posting!

Could you share some example data? I have tried replicating this scenario on my end, but when using the Read Excel Activity and saving the spreadsheets to variables, the values are recognized automatically as datetime objects, and they default to ISO 8601 format (YYYY-MM-DDT00:00:00).

ans24img1
ans24img11612×956 90.2 KB

Comparing values between excelFileA and excelFileB does return True for all of them.

If you could share some sample files, I can help you find a solution.

2 Likes
3

Hi Ivan.

Thank you for trying to help me. I need the format in both files to be exactly the same because i convert all the content of each line into a string and then compare the strings of both files to check if they are the same.

I’ve been able to solve my problem by using the following method:

new Date(variable-with-ISO-date).toLocaleDateString()

This way both dates are the same format and the comparison between both strings works perfectly.

4

Hi @cris-dsc,

That’s fantastic! Thank you so much for sharing the solution that worked for you.

I understand that you were likely dealing with text-formatted cells in Excel, while in my example my cells were formatted as dates – this would explain your different strings. The method you used is great to convert date-representing strings into a specific format.

Also keep in mind that Studio Pro includes support for the Moment library for date and time parsing and manipulation – you can use Moment syntax in any property with the “Calculate a value” option.

Thank you once again for sharing!

1 Like