Visual Basic for Applications/Date-Time String Formats

Summary

This page lists VBA code to format date-time strings and intervals. It is for use in Microsoft Excel and similar applications that run VBA code. The procedure takes a date variable and returns it as a formatted string. The parameter nOpt sets the chosen format from a set that includes dates, times, or intervals. There are two main format types in use:

• Strings containing a date, are those that display a day, month, and year, with or without times added to them. The important point here is that the number of days stored in the date variable is intended to be displayed as a date, as opposed to an integer. For example, the integer stored for 25 December 2018 is just 43459 and is only converted for display. Nearly all of the format options in the code module are of this basic type.
• Strings containing time intervals, are intended to display the number of days as an integer, as opposed to displaying it as a conventional date; that is, in the format d:h:m:s, like a stopwatch. If a date format is selected for an intended for an interval of a couple of days or so, then a very early date near 1900 will be displayed. That said, such an error might still be useful for those curious about the actual number of days associated with a particular date. One time interval format has been included to illustrate the difference in the method, while a full set of these can be found on an adjacent page.
• Date-time variable assignment examples can be found in the procedure DateAssign().

VBA Code Module

Copy the entire VBA code listing into a standard module, select a formatting option (1-15) in the top procedure , then run it.

Modifications

• 10 Jan 2019, DateTimeFormat() code modified to include one interval format.

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