Excel VBA: Dates displaying as text even though date format is set

Question

Excel 2010 Pro.

I'm having a weird formatting problem.

It's working OK in America but not after I run the code in Austria. In Austria, it ignores my date and time formatting. I can't change it after that, even manually. See how if I try different formats, the sample at the top is not changing?

It is important for the cell value to include the whole date and time, but to use the desired time and date formats.

Updated 14Nov13: The problem is described on other websites with the idea that the date somehow gets "stuck as text" and cannot be reformatted to anything else. One suggestion was to do something mathematical to the cell to force it back to non-text. (Idea: range.value = cdate(range.value) + 0) I looked closely at my cell in Austria and noticed that there are two spaces between the date and time like 14.11.2013 15:22:19. In one case I removed one of the spaces, and it snapped into the format that I wanted (14-NOV-13). However, I was not able to repeat that and will have to figure out how to do something programatically. I am not sure of the correct solution yet. It will probably involve cycling through all the date cells after I paste my variant array, and somehow fix them.

BAD (Austria)

The Sample Won't Change Even if I Select DIfferent Formats

GOOD (America)

enter image description here

MORE DETAIL: In my application, I write a date to two cells via an array, then format them using one of two custom date formats. The cell formatting is correct when I check it. When I look at the worksheet in America, it is correct like:

A1 shows 01-DEC-13 ' English
B2 shows 15:23

Then I copy the XLSM to a server in Austria and open it. It looks correct.

A1 shows 01-DEZ-13 ' German
B1 shows 15:23

Then I run my code which writes new data into those time fields and formats it. This messes up what I see in Austria, but not in America.

A1 shows 01.12.2013 15:23 ' Correct value, incorrect format
B1 shows 01.12.2013 15:23 ' Correct value, incorrect format

I debugged until I found the section that caused the problem which is when I write the data to the worksheet from an array. In debugging, I can see that when I copy the array in, all formatting is lost. That is OK.

Set Destination = myWS.Range("A" & lFirstRow)
Destination.Resize(UBound(arrIn, 1), UBound(arrIn, 2)) = arrIn

Then I run a routine that reformats the area.

If not bFormatWorksheet(myWS) then err.Raise glHandled_Error

In this routine, I try one of two ways to correct the problem. First, I try copying my desired formats from a hidden template.

myWS.Rows(lFirstRow & ":" & lLastRow).ClearFormats
wksTemplate.Rows(giHEADER_ROW + 1).Copy
myWS.Rows(lFirstRow & ":" & lLastRow).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
                                                   SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

On my American version, this yields correctly formatted cells. For instance

A1.Value = 12/5/2013 05:00:45
B1.Value = 12/5/2013 05:00:45
A1 shows 05-DEC-13 ' DD-MMM-YY Correct
B1 shows 05:00 ' HH:MM Correct

Again, it looks OK when I open the document in Austria, but not after I run any update code.

After I run the update code in Austria I see this:

A1.Value = 12/5/2013 05:00:45
B1.Value = 12/5/2013 05:00:45
A1 shows = 12/5/2013 05:00:45
B1 shows = 12/5/2013 05:00:45

and if I check the NumberFormat...

?Range("A1").NumberFormat
dd-mmm-yy
?Range("B2").NumberFormat
hh:mm

and if I right click each cell to check format I see...

A1 format TT-MMM-JJ ' Correct for Tag, Monat, Jahr
B1 format hh:mm ' Correct

Since that wasn't working, I went in and explicitly reformatted each time cell in a loop like this:

for i = lFirst to lLast
  myWS.Range("A" & i).numberformat = "DD-MMM-YY"
  myWSRange("B" & i).numberformat = "HH:MM"
next i

This did not help.

Ideas?

Thanks Shari

I'm pretty sure date-time formats rely on the system Locale setting. Does Austrian typically use . instead of / to delimit dates? That might explain it. Consider writing a string literal if you want to preserve format, indpendent of the user's Locale setting. — David Zemens
@SiddharthRout arrIn is populated by the code. I read in a data range from the worksheet. Then I manipulate it, change it, reorder it, and build my final results in a new array. Then I write the array to my worksheet and format each cell. — Shari W

Shari W Shari W · Accepted Answer · 2013-12-16T16:30:47

I finally figured this out. After hours of reading on the internet, I got a clue that led me to this solution. It's kind of voodoo and doesn't make much sense, but it solved my problem.

At the beginning of my formatting routine, I copy a formatted row from my template. This worked fine while staying in America but failed as described in Austria.

I then added date reformatting to the end of my formatting routine, shown below. This solved the problem.

   For i = lFirstRow To lLastRow
        With myWS
                ' The following line didn't work 
                '    (where gsDATE_DISPLAY_FORMAT = "DD-MMM-YYYY")
                '.Cells(i, pstColNum.cre_eta).NumberFormat = gsDATE_DISPLAY_FORMAT

                ' The following two lines work.  I don't know why, but they do.
                ' You must have the + 0!  That is the key!
                dDate = CDate(.Cells(i, pstColNum.cre_eta)) + 0
                .Cells(i, pstColNum.cre_eta) = dDate
        End With
    Next i

Excel VBA: Dates displaying as text even though date format is set

2 Answers