Excel VBA 日期格式

Excel VBA date formats

提问by gtwebb

I've got a spreadsheet that contains a number of dates. These generally appear in either mm/dd/yyyyor mm/dd/yyyy hh:mm.

我有一个包含多个日期的电子表格。这些通常出现在mm/dd/yyyy或 中mm/dd/yyyy hh:mm。

The problem is that the dates aren't always put in correctly and I want to have checks to make sure they are dates in the code.

问题是日期并不总是正确输入，我想检查以确保它们是代码中的日期。

My original thought was to use IsDateto check or CDatebut this didn't seem to work: it was still returning strings instead of dates.

我最初的想法是用来IsDate检查或CDate但这似乎不起作用：它仍然返回字符串而不是日期。

I've since set up a small experiment which shows that these functions don't work the way I expect them to. Methodology is:

从那以后，我建立了一个小实验，它表明这些函数并没有像我期望的那样工作。方法论是：

1. In a cell A1 I enter the formula =DATE(2013,10,28)
2. Cell B1 formula =A1*1which should equal a number (41575)

3. Run this little script

   Sub test()
   
   MsgBox ("Start:" & TypeName(ActiveCell.Value) & " " & IsDate(ActiveCell.Value))
   
   ActiveCell.Value = Format(ActiveCell.Value, "mm/dd/yyyy")
   MsgBox ("After format: " & TypeName(ActiveCell.Value) & " " & IsDate(ActiveCell.Value))
   
   ActiveCell.Value = CDate(ActiveCell.Value)
   MsgBox ("After Cdate: " & TypeName(ActiveCell.Value) & " " & IsDate(ActiveCell.Value))
   
   End Sub
   

1. 在单元格 A1 中，我输入公式 =DATE(2013,10,28)
2. =A1*1应等于数字的单元格 B1 公式(41575)

3. 运行这个小脚本

   Sub test()
   
   MsgBox ("Start:" & TypeName(ActiveCell.Value) & " " & IsDate(ActiveCell.Value))
   
   ActiveCell.Value = Format(ActiveCell.Value, "mm/dd/yyyy")
   MsgBox ("After format: " & TypeName(ActiveCell.Value) & " " & IsDate(ActiveCell.Value))
   
   ActiveCell.Value = CDate(ActiveCell.Value)
   MsgBox ("After Cdate: " & TypeName(ActiveCell.Value) & " " & IsDate(ActiveCell.Value))
   
   End Sub
   

When the script starts the cell is a of type date and IsDatereturns true. After it is run through Formatit is of type string but IsDatestill returns true. CDatewill also convert the cell to a string. Cell B1 will also now return 0 (since its a string*1).

当脚本启动时，单元格是日期类型并IsDate返回 true。运行后它是Format字符串类型，但IsDate仍返回true。 CDate还将单元格转换为字符串。单元格 B1 现在也将返回 0（因为它是一个字符串*1）。

So I guess to summarize the questions:

所以我想总结一下问题：

1. Why are Formatand CDatechanging my cells to strings?
2. How can I ensure that a cell will return a date value and not just a string that looks like a date?

1. 为什么Format和CDate改变我的细胞为字符串？
2. 如何确保单元格将返回日期值而不仅仅是看起来像日期的字符串？

回答by Jean-Fran?ois Corbett

It's important to distinguish between the contentof cells, their display format, the data type readfrom cells by VBA, and the data type writtento cells from VBA and how Excel automatically interprets this. (See e.g. this previous answer.) The relationship between these can be a bit complicated, because Excel will do things like interpret values of one type (e.g. string) as being a certain other data type (e.g. date) and then automatically change the display formatbased on this. Your safest bet it do everything explicitly and not to rely on this automatic stuff.

区分单元格的内容、它们的显示格式、VBA 从单元格读取的数据类型、从 VBA写入单元格的数据类型以及 Excel 如何自动解释这一点很重要。（例如参见前面的答案。）它们之间的关系可能有点复杂，因为 Excel 会将一种类型（例如字符串）的值解释为某种其他数据类型（例如日期），然后自动更改显示格式基于此。你最安全的赌注是它明确地做所有事情，而不是依赖这些自动的东西。

I ran your experiment and I don't get the same results as you do. My cell A1 stays a Date the whole time, and B1 stays 41575. So I can't answer your question #1. Results probably depend on how your Excel version/settings choose to automatically detect/change a cell's number format based on its content.

我运行了您的实验，但得到的结果与您不同。我的单元格 A1 一直保持日期，而 B1 保持 41575。所以我无法回答你的问题 #1。结果可能取决于您的 Excel 版本/设置如何选择根据其内容自动检测/更改单元格的数字格式。

Question #2, "How can I ensure that a cell will return a date value": well, not sure what you mean by "return" a date value, but if you want it to containa numerical value that is displayedas a date, based on what you write to it from VBA, then you can either:

问题 #2，“我如何确保单元格将返回日期值”：嗯，不确定“返回”日期值是什么意思，但如果您希望它包含显示为日期的数值，根据您从 VBA 写入的内容，您可以：

• Write to the cell a string value that you hope Excel will automatically interpret as a date and format as such. Cross fingers. Obviously this is not very robust. Or,

• Write a numerical value to the cell from VBA (obviously a Date type is the intended type, but an Integer, Long, Single, or Double could do as well) and explicitly set the cells' number format to your desired date format using the .NumberFormatproperty (or manually in Excel). This is much more robust.

• 向单元格写入一个字符串值，您希望 Excel 将其自动解释为日期和格式。交叉手指。显然这不是很健壮。或者，

• 从 VBA 向单元格写入一个数值（显然日期类型是预期的类型，但整数、长、单或双也可以）并使用.NumberFormat属性将单元格的数字格式显式设置为所需的日期格式（或在 Excel 中手动）。这要健壮得多。

If you want to check that existing cell contents can be displayed as a date, then here's a function that will help:

如果您想检查现有单元格内容是否可以显示为日期，那么这里有一个可以帮助您的函数：

Function CellContentCanBeInterpretedAsADate(cell As Range) As Boolean
    Dim d As Date
    On Error Resume Next
    d = CDate(cell.Value)
    If Err.Number <> 0 Then
        CellContentCanBeInterpretedAsADate = False
    Else
        CellContentCanBeInterpretedAsADate = True
    End If
    On Error GoTo 0
End Function

Example usage:

用法示例：

Dim cell As Range
Set cell = Range("A1")

If CellContentCanBeInterpretedAsADate(cell) Then
    cell.NumberFormat = "mm/dd/yyyy hh:mm"
Else
    cell.NumberFormat = "General"
End If

回答by Ken White

Formatconverts the values to strings. IsDatestill returns true because it can parse that string and get a valid date.

Format将值转换为字符串。IsDate仍然返回 true，因为它可以解析该字符串并获得有效日期。

If you don't want to change the cells to string, don't use Format. (IOW, don't convert them to strings in the first place.) Use the Cell.NumberFormat, and set it to the date format you want displayed.

如果您不想将单元格更改为字符串，请不要使用Format. （IOW，首先不要将它们转换为字符串。）使用Cell.NumberFormat, 并将其设置为您想要显示的日期格式。

ActiveCell.NumberFormat = "mm/dd/yy"   ' Outputs 10/28/13
ActiveCell.NumberFormat = "dd/mm/yyyy" ' Outputs 28/10/2013

回答by gtwebb

Thanks for the input. I'm obviously seeing some issues that aren't being replicated on others machines. Based on Jean's answer I have come up with less elegant solution that seems to work.

感谢您的投入。我显然看到了一些没有在其他机器上复制的问题。基于让的回答，我想出了一个不太优雅的解决方案，但似乎有效。

Since if I pass the cell a value directly from cdate, or just format it as a number it leaves the cell value as a string I've had to pass the date value into a numerical variable before passing that number back to the cell.

因为如果我直接从 cdate 向单元格传递一个值，或者只是将它格式化为一个数字，它会将单元格值作为一个字符串，所以我必须在将该数字传递回单元格之前将日期值传递给一个数值变量。

Function CellContentCanBeInterpretedAsADate(cell As Range) As Boolean
    Dim d As Date
    On Error Resume Next
    d = CDate(cell.Value)
    If Err.Number <> 0 Then
        CellContentCanBeInterpretedAsADate = False
    Else
        CellContentCanBeInterpretedAsADate = True
    End If
    On Error GoTo 0
End Function

Example usage:

用法示例：

Dim cell As Range
dim cvalue as double
Set cell = Range("A1")

If CellContentCanBeInterpretedAsADate(cell) Then
    cvalue = cdate(cell.value)
    cell.value = cvalue
    cell.NumberFormat = "mm/dd/yyyy hh:mm"
Else
    cell.NumberFormat = "General"
End If

回答by Craig

Use value(cellref)on the side to evaluate the cells. Strings will produce the "#Value" error, but dates resolve to a number (e.g. 43173).

value(cellref)在侧面使用以评估细胞。字符串将产生“#Value”错误，但日期解析为数字（例如43173）。

回答by Docmarti

To ensure that a cell will return a date value and not just a string that looks like a date, first you must set the NumberFormat property to a Date format, then put a real date into the cell's content.

为确保单元格将返回日期值而不仅仅是看起来像日期的字符串，首先必须将 NumberFormat 属性设置为日期格式，然后将实际日期放入单元格的内容中。

Sub test_date_or_String()
 Set c = ActiveCell
 c.NumberFormat = "@"
 c.Value = CDate("03/04/2014")
   Debug.Print c.Value & " is a " & TypeName(c.Value) 'C is a String
 c.NumberFormat = "m/d/yyyy"
   Debug.Print c.Value & " is a " & TypeName(c.Value) 'C is still a String
 c.Value = CDate("03/04/2014")
   Debug.Print c.Value & " is a " & TypeName(c.Value) 'C is a date    
End Sub