转换和创造

CDATE()

CDate() 将任何数据类型的内容转换为 Date 数据类型

Sub CDateExamples()

    Dim sample As Date

    ' Converts a String representing a date and time to a Date
    sample = CDate("September 11, 2001 12:34")
    Debug.Print Format$(sample, "yyyy-mm-dd hh:nn:ss")      ' prints 2001-09-11 12:34:00
    
    ' Converts a String containing a date to a Date
    sample = CDate("September 11, 2001")
    Debug.Print Format$(sample, "yyyy-mm-dd hh:nn:ss")      ' prints 2001-09-11 00:00:00

    ' Converts a String containing a time to a Date
    sample = CDate("12:34:56")
    Debug.Print Hour(sample)                        ' prints 12
    Debug.Print Minute(sample)                      ' prints 34
    Debug.Print Second(sample)                      ' prints 56
    
    ' Find the 10000th day from the epoch date of 1899-12-31
    sample = CDate(10000)
    Debug.Print Format$(sample, "yyyy-mm-dd")       ' prints 1927-05-18
    
End Sub

请注意,VBA 还有一个松散类型的 CVDate(),其功能与 CDate() 功能相同,而不是返回日期类型 Variant 而不是强类型 Date。当传递给 Date 参数或分配给 Date 变量时,CDate() 版本应该是首选的,当传递给 Variant 参数或分配给 Variant 变量时,首选 CVDate() 版本。这避免了隐式类型转换。

DateSerial()

DateSerial() 函数用于创建日期。它返回指定年,月和日的 Date

句法:

DateSerial ( year, month, day ) 

年,月,日参数为有效整数(年份为 100 至 9999,月份为 1 至 12,日期为 1 至 31)。

例子

Sub DateSerialExamples()

    ' Build a specific date
    Dim sample As Date
    sample = DateSerial(2001, 9, 11)
    Debug.Print Format$(sample, "yyyy-mm-dd")                   ' prints 2001-09-11
    
    ' Find the first day of the month for a date.
    sample = DateSerial(Year(sample), Month(sample), 1)
    Debug.Print Format$(sample, "yyyy-mm-dd")                   ' prints 2001-09-11
    
    ' Find the last day of the previous month.
    sample = DateSerial(Year(sample), Month(sample), 1) - 1
    Debug.Print Format$(sample, "yyyy-mm-dd")                   ' prints 2001-09-11
    
End Sub

请注意,DateSerial() 将接受无效日期并从中计算有效日期。这可以创造性地用于:

正例

Sub GoodDateSerialExample()

    'Calculate 45 days from today
    Dim today As Date
    today = DateSerial (2001, 9, 11)
    Dim futureDate As Date
    futureDate = DateSerial(Year(today), Month(today), Day(today) + 45)
    Debug.Print Format$(futureDate, "yyyy-mm-dd")            'prints 2009-10-26

End Sub

但是,尝试从未经验证的用户输入创建日期时,更有可能导致悲伤:

反面例子

Sub BadDateSerialExample()

    'Allow user to enter unvalidate date information
    Dim myYear As Long
    myYear = InputBox("Enter Year")                                         
            'Assume user enters 2009
    Dim myMonth As Long
    myMonth = InputBox("Enter Month")                                       
            'Assume user enters 2
    Dim myDay As Long
    myDay = InputBox("Enter Day")                                           
            'Assume user enters 31
    Debug.Print Format$(DateSerial(myYear, myMonth, myDay), "yyyy-mm-dd")   
            'prints  2009-03-03

End Sub