Excel VBA：即使设置了日期格式，日期也会显示为文本

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

提问by Shari W

Excel 2010 Pro.

Excel 2010 专业版。

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.

13 年 11 月 14 日更新：其他网站上描述了该问题，其想法是日期以某种方式“卡在文本中”并且无法重新格式化为其他任何内容。一个建议是对单元格做一些数学运算以强制它返回非文本。（想法：range.value = cdate(range.value) + 0）我仔细查看了我在奥地利的单元格，注意到日期和时间之间有两个空格，如 14.11.2013 15:22:19。在一种情况下，我删除了其中一个空格，然后它变成了我想要的格式 (14-NOV-13)。但是，我无法重复这一点，并且必须弄清楚如何以编程方式做某事。我还不确定正确的解决方案。在粘贴我的变体数组后，它可能涉及循环遍历所有日期单元格，并以某种方式修复它们。

BAD (Austria)

坏（奥地利）

GOOD (America)

好（美国）

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

• A1 显示 01-DEC-13 ' 英文
• B2 显示 15:23

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

然后我将 XLSM 复制到奥地利的服务器并打开它。它看起来是正确的。

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

• A1显示01-DEZ-13'德语
• B1 显示 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

• A1 显示 01.12.2013 15:23 ' 正确的值，不正确的格式
• B1 显示 01.12.2013 15:23 ' 正确的值，不正确的格式

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

• A1.Value = 12/5/2013 05:00:45
• B1.Value = 12/5/2013 05:00:45
• A1 显示 05-DEC-13 ' DD-MMM-YY 正确
• B1 显示 05:00 ' HH:MM 正确

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

• A1.Value = 12/5/2013 05:00:45
• B1.Value = 12/5/2013 05:00:45
• A1 显示 = 12/5/2013 05:00:45
• B1 显示 = 12/5/2013 05:00:45

and if I check the NumberFormat...

如果我检查 NumberFormat ...

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

• ?Range("A1").NumberFormat
• 日-月-年
• ?Range("B2").NumberFormat
• 时：分

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

• A1 格式 TT-MMM-JJ ' 正确用于 Tag、Monat、Jahr
• B1 格式 hh:mm ' 正确

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

谢谢莎莉

回答by Shari W

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

回答by Bill N.

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

我不想肯定地说有一个 VBA 解决方案，因为它可能只是软件的本地化问题......但也许您可以通过从每个单元格中明确剥离日期/时间的不需要部分来解决这个问题在格式化之前。就像是

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.

这实际上可能不会修复格式，实际上可能会导致字段在格式错误时报告错误信息（例如 A1 中的“12/5/2013 00:00:00”和“12/30/1899 05” B1 中的 :00:45'），但我会尝试一下，看看会发生什么。您可能希望忽略将其全部格式化为数字并将其保留为格式化字符串。这当然取决于没有其他代码要求将时间保存在 A 列中并将日期保存在 B 列中的理解。如果有，那么这不是一个正确的解决方案。