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

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

   <source lang="vb">

Sub formatDemo11()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub formatDemo30()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub formatDemo34()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub formatDemo32()

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

End Sub

</source>
   
  

c - Displays the date

   <source lang="vb">

Sub formatDemo5()

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

End Sub

</source>
   
  

/ Date separator (also locale-dependent).

   <source lang="vb">

Sub formatDemo3()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub formatDemo10()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub formatDemo9()

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

End Sub

</source>
   
  

dddd - Displays the full name of the day.

   <source lang="vb">

Sub formatDemo8()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub formatDemo7()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub formatDemo6()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub formatDemo4()

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

End Sub

</source>
   
  

For example, the following statement returns Saturday, April:

   <source lang="vb">

Sub formatDemo35()

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

End

</source>
   
  

Format(datDateTime, "dd mmm yy")

   <source lang="vb">

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

</source>
   
  

Format(datDateTime, "Long Date")

   <source lang="vb">

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

</source>
   
  

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

   <source lang="vb">

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

</source>
   
  

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

   <source lang="vb">

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

</source>
   
  

format(Now, "ddd")

   <source lang="vb">

Sub dateFunctions6()

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

End Sub

</source>
   
  

format(Now, "dddd")

   <source lang="vb">

Sub dateFunctions7()

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

End Sub

</source>
   
  

format(Now, "ddddd")

   <source lang="vb">

Sub dateFunctions8()

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

End Sub

</source>
   
  

format(Now, "dddddd")

   <source lang="vb">

Sub dateFunctions9()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub dateFunctions4()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub dateFunctions3()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub dateFunctions()

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

End Sub

</source>
   
  

format(Now, "mmmm d, yyyy")

   <source lang="vb">

Sub dateFunctions5()

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

End Sub

</source>
   
  

Format(Now, "ttttt")

   <source lang="vb">

Sub dateFunctions()

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

End Sub

</source>
   
  

Format the date type value

   <source lang="vb">

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

</source>
   
  

Format time as mmmm_yyyy

   <source lang="vb">

Sub FormatNow()

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

End Sub

</source>
   
  

formatting the date and time with format function

   <source lang="vb">

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

</source>
   
  

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

   <source lang="vb">

Sub formatDemo23()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub formatDemo24()

   Debug.Print Format(#4/1/2006 12:36:54 PM#, "Hh")
</source>
   
  

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

   <source lang="vb">

Sub formatDemo14()

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

End Sub

</source>
   
  

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.

   <source lang="vb">

Sub formatDemo15()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub formatDemo16()

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

End Sub

</source>
   
  

mmmm - Displays the full name of the month.

   <source lang="vb">

Sub formatDemo17()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub formatDemo25()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub formatDemo26()

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

End Sub

</source>
   
  

oooo - Displays the full localized name of the month.

   <source lang="vb">

Sub formatDemo18()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub formatDemo19()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub formatDemo27()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub formatDemo28()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub formatDemo2()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub formatDemo29()

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

End Sub

</source>
   
  

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

   <source lang="vb">

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.

</source>
   
  

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

   <source lang="vb">

Sub formatDemo12()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub formatDemo13()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub formatDemo20()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub formatDemo21()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub formatDemo22()

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

End Sub

</source>