I’ve hit a major issue formatting dates in Excel using the InvokeVBA activity.
My automation has this workflow:
Open a CSV file
Save as an Excel XLSX workbook
Open the Excel workbook
Format the date column data as dd/MM/yyyy using some vba and InvokeVBA activity.
The date format is listed in Excel as dd/MM/yyyy but when I compare the dates in the spreadsheet with the dates in the CSV, I can see that the format is actually: MM/dd/yyyy.
My vba code which is invoked by the InvokeVBA activity is:
The CSV dates look like this:
The resulting Excel Spreadsheet dates look like this (note the 2nd, 3rd and 4th dates in US format when compared with the CSV and the first date marked by Excel as invalid):
The Excel date format settings are:
What is the best way to format dates in Excel as UK and have them actually formatted as UK Dates if that makes sense?
It seems that you have trouble getting an answer to your question in the first 24 hours.
Let us give you a few hints and helpful links.
First, make sure you browsed through our Forum FAQ Beginner’s Guide. It will teach you what should be included in your topic.
You can check out some of our resources directly, see below:
Always search first. It is the best way to quickly find your answer. Check out the icon for that.
Clicking the options button will let you set more specific topic search filters, i.e. only the ones with a solution.
Topic that contains most common solutions with example project files can be found here.
Read our official documentation where you can find a lot of information and instructions about each of our products:
Meet us and our users on our Community Slack and ask your question there.
Hopefully this will let you easily find the solution/information you need. Once you have it, we would be happy if you could share your findings here and mark it as a solution. This will help other users find it in the future.
Thank you for helping us build our UiPath Community!
The timestamp in Excel somehow defaults to the time format of your machine. I’m saying this because one of our Macros malfunctioned when run by our counterparts in UK causing the Robot to fail. It could not find the files that were normally date-stamped “MMddyyyy” because the dates read from the Excel had fallen back on the UK format with timestamps changed to ddMMyyyy despite being set to US formats in the Macro.
We got over it by setting the Machine time zone to US EST.
Excel VBA is not my expertise, but you may have to retain the dates in natural string format as long as they are in the Excel and do the actual conversion to the UK format at the point of use.
Not to forget the nasty logic bugs it can cause because both, 10/08/2021 and 08/10/2021 are valid dates and can push results into a wrong quarter if one date is interpreted as the other.
Hopefully other Excel/VBA Experts can help you out here on the forum.
