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?
Many thanks in advance!