Forum Discussion
Excel Date Format
I have a vba script that pulls various fields out of a number of excel sheets into a single table. One of those fields is a date which is formatted as Date > *14/04/2012 and Locale= English(United Kingdom).
Some cells (not all) seem to convert to American format when running the script so 08/12/2022 (as shown in the excel file) becomes 12/08/2022 in the vba output sheet. I've even tried changing the format to DD/MMM/YYYY and it still converts to American format when output to the VBA sheet,
To repeat, this isn't happening on all occasions, only certain sheets...but the format of the cells is exactly the same.
One thing I have noticed is that if I changed the format of the excel cell to a number the ones that erroneously convert to American format will display a number, while the ones that remain UK wont convert and continue to display a date.😞
3 Replies
If cells still look like a date when you format it as General or Number, that means that Excel sees the cell value as text, nor as a date...
Does this happen on the source sheets from which you pull the data, or on the target sheet?
- I have a Date validation trap on the field so strange that it is seeing it as text.
The ones that stay as text are doing so in the target sheet - but remain 'UK'. The ones that do covert to numbers if formatted are swapping to American format 😞Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
