应用程序/日期时间字符串格式的 Visual Basic
此页面列出了用于格式化日期时间字符串和间隔的 VBA 代码。它用于在运行 VBA 代码的 Microsoft Excel 和类似应用程序中。该过程接受一个日期变量并将其作为格式化的字符串返回。参数nOpt从包含日期、时间或间隔的集合中设置选择的格式。有两种主要的格式类型正在使用
- 包含日期的字符串，是指显示日、月和年的字符串，可以选择添加时间。这里的重要一点是，存储在日期变量中的天数旨在显示为日期，而不是整数。例如，2018 年 12 月 25 日存储的整数只是 43459，它只用于显示而进行转换。代码模块中的几乎所有格式选项都属于这种基本类型。
- 包含时间间隔的字符串，旨在将天数显示为整数，而不是将其显示为常规日期；也就是说，以 d:h:m:s 格式显示，就像秒表一样。如果为一个持续几天左右的时间间隔选择了日期格式，则会显示 1900 年附近的非常早的日期。也就是说，这种错误对于那些好奇与特定日期相关的实际天数的人来说仍然可能是有用的。已包含一个时间间隔格式来说明该方法的差异，而完整的此类格式集可以在相邻页面上找到。
- 日期时间变量赋值示例可以在 DateAssign() 过程中找到。
将整个 VBA 代码列表复制到标准模块中，在顶部过程中选择一个格式选项（1-15），然后运行它。
- 2019 年 1 月 10 日，DateTimeFormat() 代码已修改以包含一个间隔格式。
Option Explicit
Sub testDateFormats()
'Run this to format a date-time
Dim dDate As Date, nOpt As Integer
'set test date here - examples
dDate = #1/9/2019 1:45:02 PM#
'set format option 1-14 for dates
'and 15 to format as a time interval
nOpt = 14
MsgBox DateTimeFormat(dDate, nOpt)
End Sub
Function DateTimeFormat(dDate As Date, Optional ByVal nOpt As Integer = 1) As String
'Returns dDate as a date-time display string in function name.
'Optional format choice with nOpt= (1-14) for dates, and nOpt=(15) for intervals.
Dim sOut As String
If Not IsDate(dDate) Then
MsgBox "Parameter not a date - closing"
Exit Function
End If
Select Case nOpt 'returns for #1/9/2019 1:45:02 PM#
'(9th January 2019 at 13:45:02)
Case 1
sOut = Format(dDate, "dd\/mm\/yy") '09/01/19
Case 2
sOut = Format(dDate, "d mmm yy") '9 Jan 19
Case 3
sOut = Format(dDate, "dd:mm:yy") '09:01:19
Case 4
sOut = Format(dDate, "d mmmm yyyy") '9 January 2019
Case 5
sOut = Format(dDate, "mmmm d, yyyy") 'January 9, 2019
Case 6
sOut = Format(dDate, "dddd, dd\/mm\/yyyy") 'Wednesday, 09/01/2019
Case 7
sOut = Format(dDate, "dddd, mmm d yyyy") 'Wednesday, Jan 9 2019
Case 8
sOut = Format(dDate, "dddd, d mmmm yyyy") 'Wednesday, 9 January 2019
Case 9
sOut = Format(dDate, "y") '9, day in year (1-365)
Case 10
sOut = sOut = Format(dDate, "h:m:s") '13:45:2 'no leading zeros
Case 11
sOut = Format(dDate, "h:m:s AM/PM") '1:45:2 PM 'no leading zeros
Case 12
sOut = Format(dDate, "hh:mm:ss") '13:45:02 'leading zeros added
Case 13
sOut = Format(dDate, "ddmmyy_hhmmss") '090119_134502, leading zeros added
Case 14
sOut = Format(dDate, "dddd, d mmmm yyyy, hh:mm:ss AM/PM") 'Wednesday, 9 January 2019, 01:45:02 PM
Case 15
sOut = Format(Int(CSng(dDate)), "###00") & ":" & Format(dDate, "hh:nn:ss") 'time interval format
Case Else
MsgBox "Option out of bounds in DateTimeFormat() - closing"
End Select
DateTimeFormat = sOut
End Function
Sub DateAssign()
'date-time assignment examples
Dim dD1 As Date, dD2 As Date, dD3 As Date
Dim dD4 As Date, dD5 As Date, dD6 As Date
Dim dD7 As Date, dD8 As Date, dD9 As Date
Dim dD10 As Date, dD11 As Date, dd12 As Date
'These three assignment methods are equivalent
'and will display 25 Dec 2018 only
dD1 = #12/25/2018# 'literal
dD2 = DateValue("25 Dec 2018") 'string
dD3 = DateSerial(2018, 12, 25) 'integer
'These three assignment methods are equivalent
'and will display 10:05:07 AM only
dD4 = #10:05:07 AM# 'literal
dD5 = TimeValue("10:05:07") 'string
dD6 = TimeSerial(10, 5, 7) 'integer
'These six combined methods are equivalent
'and will display 25 Dec 2018 10:05:07 AM
dD7 = #12/25/2018 10:05:07 AM#
dD8 = dD1 + dD4
dD9 = DateValue("25 dec 2018") + TimeValue("10:05:07")
dD10 = DateSerial(2018, 12, 23) + TimeSerial(58, 4, 67)
dD11 = dD1 + (0 / 1) + (10 / 24) + (5 / 1440) + (7 / 86400)
dd12 = DateValue("27 dec 2018") - (2 / 1) + (10 / 24) + (5 / 1440) + (7 / 86400)
'confirm equality of results in immediate window
Debug.Print CStr(dD7) = CStr(dD8)
Debug.Print CStr(dD8) = CStr(dD9)
Debug.Print CStr(dD9) = CStr(dD10)
Debug.Print CStr(dD10) = CStr(dD11)
Debug.Print CStr(dD11) = CStr(dd12)
Debug.Print dD1
Debug.Print dD4
Debug.Print dD7
MsgBox dD7
End Sub