VBA not changing data format - but manual update works

[aiden]

Hi All,

On a weekly basis I receive a workbook of 15 worksheets that contain some date columns. The date formats on the worksheet columns (and my computer settings) are set to dd/mm/yyyy. However, Excel is not recognizing dates that don't fit the the US standard format of mm/dd/yyyy. Excel is treating them as text instead.

I have two manual tricks for forcing Excel to recognize the the values as dates. I can do a Find/Replace on the entire column. If I find "/" (forward slash) and replace with a "/" (forward slash), all values are now recognized as dates. I can also copy a cell value of 1 and do a paste special on the column (paste the value, and multiply) and again Excel recognizes the dates.

Here's my problem, I've tried to create macros to automate one or both of these tricks but for some reason they don't work. I'm not doing anything fancy, in fact, I obtained the code using Excel to record the macro. Even though it worked via the user interface, the VBA code won't work. Can anyone suggest some VBA code that will force Excel to recognize dates that are in dd/mm/yyyy format instead of mm/dd/yyyy.

Here's what I tried.

Find/Replace

Columns("t:t").Select
Selection.Replace What:="/", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=True

Copy/Paste Special

Range("R2").Select
ActiveCell.FormulaR1C1 = "1"
Range("R2").Select
Selection.Copy
Columns("I:I").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False

Thanks in Advance.

Aiden

 

[SkipVought]

Hi,

Excel's default is m/d/yyyy.

If your dates are being imported as d/m/yyyy, then you have a HUGE problem, because Excel takes any VALID d/m combination and interprets it as m/d and any INVALID d/m combination as leaves it as text.

Please be aware that dates are really NUMBERS. What you see is a DISPLAY FORMAT.

How is this workbook originating? I'd want to be absolutely sure that all the date date is unambiguously correct. The BEST UNAMBIGUOUS way to ENTER dates is yyyy/mm/dd.

faq68-5827

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}