VBA/Excel/Access/Word/Date Functions/Date Format

aaaa - Displays the full, localized name of the day.

 
Sub formatDemo11()
    Debug.Print Format(#4/1/2006 12:36:54 PM#, "aaaa")
End Sub

AM/PM - Uses the 12-hour clock and displays AM or PM as appropriate.

 
Sub formatDemo30()
    Debug.Print Format(#4/1/2006 12:36:54 PM#, "AM")
End Sub

AMPM - Uses the 12-hour clock and displays the AM or PM string literal defined for the system.

 
Sub formatDemo34()
    Debug.Print Format(#4/1/2006 12:36:54 PM#, "AMPM")
End Sub

A/P - Uses the 12-hour clock and displays A or P as appropriate.

 
Sub formatDemo32()
    Debug.Print Format(#4/1/2006 12:36:54 PM#, "A")
End Sub

c - Displays the date

 
Sub formatDemo5()
    Debug.Print Format(#4/1/2006 12:36:54 PM#, "c")
End Sub

/ Date separator (also locale-dependent).

 
Sub formatDemo3()
    Debug.Print Format(#4/1/2006 12:36:54 PM#, "dddddd")
End Sub

dddddd - Displays the complete date (day, month, and year) in the system"s long date format.

 
Sub formatDemo10()
    Debug.Print Format(#4/1/2006 12:36:54 PM#, "dddddd")
End Sub

ddddd - Displays the complete date (day, month, and year) in the system"s short date format.

 
Sub formatDemo9()
    Debug.Print Format(#4/1/2006 12:36:54 PM#, "ddddd")
End Sub

dddd - Displays the full name of the day.

 
Sub formatDemo8()
    Debug.Print Format(#4/1/2006 12:36:54 PM#, "dddd")
End Sub

ddd - Displays the day as a three-letter abbreviation (Sun, Mon, Tue, Wed, Thu, Fri, Sat) with no period.

 
Sub formatDemo7()
    Debug.Print Format(#4/1/2006 12:36:54 PM#, "ddd")
End Sub

dd - Displays the date with a leading zero for single-digit numbers (01 to 31).

 
Sub formatDemo6()
    Debug.Print Format(#4/1/2006 12:36:54 PM#, "dd")
End Sub

d - Displays the date (1 to 31) without a leading zero for single-digit numbers.

 
Sub formatDemo4()
    Debug.Print Format(#4/1/2006 12:36:54 PM#, "d")
End Sub

For example, the following statement returns Saturday, April:

 
Sub formatDemo35()
    Debug.Print Format(#4/1/2006 12:36:54 PM#, "dddddd")
End

Format(datDateTime, "dd mmm yy")

 
Sub dateFormat2()
    Dim datDateTime As Date
    datDateTime = #11/30/1998 10:54:17 AM#
    Debug.Print datDateTime
    Debug.Print format(datDateTime, "dd mmm yy")
End Sub

Format(datDateTime, "Long Date")

 
Sub dateFormat1()
    Dim datDateTime As Date
    datDateTime = #11/30/1998 10:54:17 AM#
    Debug.Print datDateTime
    Debug.Print format(datDateTime, "Long Date")
End Sub

Format(datDateTime, "mm dddd hh:mm")

 
Sub dateFormat()
    Dim datDateTime As Date
    datDateTime = #11/30/1998 10:54:17#
    Debug.Print datDateTime
    Debug.Print Format(datDateTime, "mm dddd hh:mm")      
End Sub

Format(datDateTime, "yyyy-mm-dd")

 
Sub dateFormat3()
    Dim datDateTime As Date
    datDateTime = #11/30/1998 10:54:17 AM#
    Debug.Print datDateTime
    Debug.Print format(datDateTime, "yyyy-mm-dd")
End Sub

format(Now, "ddd")

 
Sub dateFunctions6()
    Debug.Print "ddd: " & format(Now, "ddd")
End Sub

format(Now, "dddd")

 
Sub dateFunctions7()
    Debug.Print "dddd: " & format(Now, "dddd")
End Sub

format(Now, "ddddd")

 
Sub dateFunctions8()
    Debug.Print "ddddd: " & format(Now, "ddddd")
End Sub

format(Now, "dddddd")

 
Sub dateFunctions9()
    Debug.Print "dddddd: " & format(Now, "dddddd")
End Sub

format(Now, "d-mmmm-yy")

 
Sub dateFunctions4()
    Debug.Print "d-mmmm-yy: " & format(Now, "d-mmmm-yy")
End Sub

format(Now, "d-mmm-yy")

 
Sub dateFunctions3()
    Debug.Print "d-mmm-yy: " & format(Now, "d-mmm-yy")
End Sub

Format(Now, "Hh:Nn:Ss AM/PM")

 
Sub dateFunctions()
    Debug.Print "Hh:Nn:Ss AM/PM: " & Format(Now, "Hh:Nn:Ss AM/PM")
End Sub

format(Now, "mmmm d, yyyy")

 
Sub dateFunctions5()
    Debug.Print "mmmm d, yyyy: " & format(Now, "mmmm d, yyyy")
End Sub

Format(Now, "ttttt")

 
Sub dateFunctions()
    Debug.Print "ttttt: " & Format(Now, "ttttt")
End Sub

Format the date type value

 
Sub FindDates()
    On Error GoTo errorHandler
    Dim startDate As String
    Dim stopDate As String
    startDate = Format("12/12/1900", "mm/??/yy")
    stopDate = Format("12/12/2000", "mm/??/yy")
    MsgBox startDate
   
    End
    errorHandler:
    MsgBox "There has been an error:  " & Error() & Chr(13) _
        & "Ending Sub.......Please try again", 48
End Sub

Format time as mmmm_yyyy

 
Sub FormatNow()
    Dim myWorksheetName As String
    myWorksheetName = Format(Now, "mmmm_yyyy")
    MsgBox myWorksheetName
End Sub

formatting the date and time with format function

 
Sub dateFunctions()
   Dim strDateString As String
   strDateString = "m/d/yy: " & Format(Now, "m/d/yy") & vbCrLf & _
                   "d-mmm-yy: " & Format(Now, "d-mmm-yy") & vbCrLf & _
                   "d-mmmm-yy: " & Format(Now, "d-mmmm-yy") & vbCrLf & _
                   "mmmm d, yyyy: " & Format(Now, "mmmm d, yyyy") & vbCrLf & _
                   "ddd: " & Format(Now, "ddd") & vbCrLf & _
                   "dddd: " & Format(Now, "dddd") & vbCrLf & _
                   "ddddd: " & Format(Now, "ddddd") & vbCrLf & _
                   "dddddd: " & Format(Now, "dddddd") & vbCrLf & _
                   "Hh:Nn:Ss AM/PM: " & Format(Now, "Hh:Nn:Ss AM/PM") & vbCrLf & _
                   "ttttt: " & Format(Now, "ttttt") & vbCrLf & _
                   "vbShortDate: " & FormatDateTime(Now, vbShortDate) & vbCrLf & _
                   "vbLongDate: " & FormatDateTime(Now, vbLongDate) & vbCrLf & _
                   "vbGeneralDate: " & FormatDateTime(Now, vbGeneralDate)
   msgBox strDateString
End Sub

h - Displays a number from 0 to 23 giving the hour.

 
Sub formatDemo23()
    Debug.Print Format(#4/1/2006 12:36:54 PM#, "h")
End Sub

Hh - Displays a number from 00 to 23 giving the two-digit hour.

 
Sub formatDemo24()
    Debug.Print Format(#4/1/2006 12:36:54 PM#, "Hh")

m - Displays an integer from 1 to 12 giving the number of the month without a leading zero on single-digit months.

 
Sub formatDemo14()
    Debug.Print Format(#4/1/2006 12:36:54 PM#, "m")
End Sub

mm - Displays a number from 01 to 12 giving the two-digit number of the month. When used after h returns minutes instead of months.

 
Sub formatDemo15()
    Debug.Print Format(#4/1/2006 12:36:54 PM#, "mm")
End Sub

mmm - Displays the month as a three-letter abbreviation (except for May) without a period.

 
Sub formatDemo16()
    Debug.Print Format(#4/1/2006 12:36:54 PM#, "mmm")
End Sub

mmmm - Displays the full name of the month.

 
Sub formatDemo17()
    Debug.Print Format(#4/1/2006 12:36:54 PM#, "mmmm")
End Sub

N - Displays a number from 0 to 60 giving the minute.

 
Sub formatDemo25()
    Debug.Print Format(#4/1/2006 12:36:54 PM#, "N")
End Sub

Nn - Displays a number from 00 to 60 giving the two-digit minute.

 
Sub formatDemo26()
    Debug.Print Format(#4/1/2006 12:36:54 PM#, "Nn")
End Sub

oooo - Displays the full localized name of the month.

 
Sub formatDemo18()
    Debug.Print Format(#4/1/2006 12:36:54 PM#, "oooo")
End Sub

q - Displays a number from 1 to 4 giving the quarter of the year.

 
Sub formatDemo19()
    Debug.Print Format(#4/1/2006 12:36:54 PM#, "q")
End Sub

S - Displays a number from 0 to 60 giving the second.

 
Sub formatDemo27()
    Debug.Print Format(#4/1/2006 12:36:54 PM#, "S")
End Sub

Ss - Displays a number from 00 to 60 giving the two-digit second.

 
Sub formatDemo28()
    Debug.Print Format(#4/1/2006 12:36:54 PM#, "Ss")
End Sub

: - Time separator (typically a colon, but this depends on the locale).

 
Sub formatDemo2()
    Debug.Print Format(#4/1/2006 12:36:54 PM#, "dddddd")
End Sub

ttttt - Displays the full time (hour, minute, and second) in the system"s default time format.

 
Sub formatDemo29()
    Debug.Print Format(#4/1/2006 12:36:54 PM#, "tttt")
End Sub

Using the Format Function to Format an Expression: Format(expression[, format[, firstdayofweek[, firstweekofyear]]])

 
 
Constant             Value      Year Starts with Week         
vbUseSystem          0          Use the system setting.        
vbFirstJan1          1          The week in which January 1 falls (the default setting).        
vbFirstFourDays      2          The first week with a minimum of four days in the year.        
vbFirstFullWeek      3          The first full week (7 days) of the year.

w - Displays an integer from 1 (Sunday) to 7 (Monday) containing the day of the week.

 
Sub formatDemo12()
    Debug.Print Format(#4/1/2006 12:36:54 PM#, "w")
End Sub

ww - Displays an integer from 1 to 54 giving the number of the week in the year.

 
Sub formatDemo13()
    Debug.Print Format(#4/1/2006 12:36:54 PM#, "ww")
End Sub

y - Displays an integer from 1 to 366 giving the day of the year.

 
Sub formatDemo20()
    Debug.Print Format(#4/1/2006 12:36:54 PM#, "y")
End Sub

yy - Displays a number from 00 to 99 giving the two-digit year.

 
Sub formatDemo21()
    Debug.Print Format(#4/1/2006 12:36:54 PM#, "yy")
End Sub

yyyy - Displays a number from o iving the four-digit year.

 
Sub formatDemo22()
    Debug.Print Format(#4/1/2006 12:36:54 PM#, "yyyy")
End Sub