Hi I'm struggling with the way Excel treats dates and cannot find a straightforward logic that works 100%
Firstly
I was trying to import a sheet from a .csv file through VBA which contained Uk date fields:
When opening it through explorer it works fine, but through VBA it was treating the dates as if they were in american format (mm/dd/yyyy) and converting them into uk (which they already were, so it was actualy making them wrong), but for those dates that it couldn't convert (more than 12 days) it left them as general format even though local settings on my system are UK.
I fixed this through adding the "local:=True" qualifier when opening the worksheet I was copying from, forcing VBA to treat them as UK.
Secondly
However, I also have a piece if code further on, which goes through each cell to ensure that the date format has a 4 digit code for the year (see below). At this point VBA then again thinks that the dates are shown in american format and tries to convert them back to UK. Even though if you pause the code before this and manually check the formatting of the sheet, it shows as being correctly formatted to UK already.
I can write something to manually seperate out the dates and do a long winded check and correction for 2 digit or 4 digit dates, but I want to understand why Excel is being inconsistent and if I am missing something that would be much easier.
All help greatly appreciated.
Firstly
I was trying to import a sheet from a .csv file through VBA which contained Uk date fields:
When opening it through explorer it works fine, but through VBA it was treating the dates as if they were in american format (mm/dd/yyyy) and converting them into uk (which they already were, so it was actualy making them wrong), but for those dates that it couldn't convert (more than 12 days) it left them as general format even though local settings on my system are UK.
I fixed this through adding the "local:=True" qualifier when opening the worksheet I was copying from, forcing VBA to treat them as UK.
Secondly
However, I also have a piece if code further on, which goes through each cell to ensure that the date format has a 4 digit code for the year (see below). At this point VBA then again thinks that the dates are shown in american format and tries to convert them back to UK. Even though if you pause the code before this and manually check the formatting of the sheet, it shows as being correctly formatted to UK already.
Code:
Set WkbTemp = Workbooks.Open(Filename:=FilesToOpen, local:=True)
LSht = WkbAll.Sheets.Count
WkbTemp.Sheets(1).Copy After:=WkbAll.Sheets(LSht)
Set SSht = ActiveSheet
'Reformat Dates
For Dts = SFRow To SlRow
SSht.Range("E" & Dts).Value = Format(SSht.Range("E" & Dts).Value, "dd/mm/yyyy")
Next DtsI can write something to manually seperate out the dates and do a long winded check and correction for 2 digit or 4 digit dates, but I want to understand why Excel is being inconsistent and if I am missing something that would be much easier.
All help greatly appreciated.
