CODEWITHSUNDEEP
excelvba
Shari W
Shari W

Reputation: 537

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

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:

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

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.

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

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:

and if I check the NumberFormat...

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

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

Upvotes: 0

Views: 6932

Answers (2)

Shari W
Shari W

Reputation: 537

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

Upvotes: 4

Bill N.
Bill N.

Reputation: 218

I wouldn't want to say for certain that there is a VBA solution as it could simply be a localization issue with the software... but perhaps you can fix the issue by explicitly stripping the unwanted portion of the date/time from each cell before you format it. Something like

myWS.Range("A" & i).value = Format(myWS.Range("A" & i).value,"DD-MMM-YY")
myWS.Range("A" & i).numberformat = "DD-MMM-YY"
myWS.Range("B" & i).value = Format(myWS.Range("B" & i).value,"HH:MM")
myWSRange("B" & i).numberformat = "HH:MM"

This might not actually fix the formatting and may, in fact, cause the fields to report bad information when in the wrong format (for example '12/5/2013 00:00:00' in A1 and '12/30/1899 05:00:45' in B1), but I would give it a try and see what happens. You might want to ignore formatting this as a number all together and leaving it as a formatted string. This of course hinges upon the understand that no other code demands that the time is preserved in column A and the date is preserved in column B. If there is, then this is not a proper solution.

Upvotes: 1

Related Questions