應用程式/日期時間字串格式的 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