VBA Date formatting inconsistencies

[Shaft120]

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.

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 Dts

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.

 

[Johnny C]

Right, someone explain this to me please!

Date on sheet to start with:
01/07/2011

From locals window, var of variant type assigned to data on worksheet:
#07/01/2011#
Type variant/date

I do the replace to replace potential '.' with '/' ...
From locals window, var of variant type assigned to data on worksheet:
#01/07/2011#
Type variant/date

So, replacing the '.' with '/' is causing it to change how XL interprets the format? Bizarre...

 

[RoryA]

See my first comment about VBA and the US.

 

[Johnny C]

And to make life interesting...

try this:

Sub freplace()
ActiveCell.Value = Replace(ActiveCell.Value, ".", "/")
End Sub

attach to a button on a new blank worksheet.

in a cell, enter 10/1 (10th Jan) - should appear as 10-Jan

Leave the cell selected, and click the button.
10-Jan changes to 01-Oct

Clicking the button again - then changes it back to 10-Jan

what's going on?

I think I'll go and microwave my head for a while.

 

[Johnny C]

Oh well, problem solved now.

Trying this:-

ActiveCell.Value = Replace(Replace(ActiveCell.Value, ".", "/"), ".", "/")

did exactly the same. Excel doesn't try and evaluate until the VBA command has executed.

Trying ths works...

ActiveCell.Value = Replace(ActiveCell.Value, ".", "/")
ActiveCell.Value = Replace(ActiveCell.Value, ".", "/")

in that it turns Jan 10th to Jan 10th, and correctly interprets 10.01.2011 to 10/1/2011 as a date (Excel treats SAP format as a string)