VBA/Excel/Access/Word/Date Functions/Date Format
Содержание
- 1 aaaa - Displays the full, localized name of the day.
- 2 AM/PM - Uses the 12-hour clock and displays AM or PM as appropriate.
- 3 AMPM - Uses the 12-hour clock and displays the AM or PM string literal defined for the system.
- 4 A/P - Uses the 12-hour clock and displays A or P as appropriate.
- 5 c - Displays the date
- 6 / Date separator (also locale-dependent).
- 7 dddddd - Displays the complete date (day, month, and year) in the system"s long date format.
- 8 ddddd - Displays the complete date (day, month, and year) in the system"s short date format.
- 9 dddd - Displays the full name of the day.
- 10 ddd - Displays the day as a three-letter abbreviation (Sun, Mon, Tue, Wed, Thu, Fri, Sat) with no period.
- 11 dd - Displays the date with a leading zero for single-digit numbers (01 to 31).
- 12 d - Displays the date (1 to 31) without a leading zero for single-digit numbers.
- 13 For example, the following statement returns Saturday, April:
- 14 Format(datDateTime, "dd mmm yy")
- 15 Format(datDateTime, "Long Date")
- 16 Format(datDateTime, "mm dddd hh:mm")
- 17 Format(datDateTime, "yyyy-mm-dd")
- 18 format(Now, "ddd")
- 19 format(Now, "dddd")
- 20 format(Now, "ddddd")
- 21 format(Now, "dddddd")
- 22 format(Now, "d-mmmm-yy")
- 23 format(Now, "d-mmm-yy")
- 24 Format(Now, "Hh:Nn:Ss AM/PM")
- 25 format(Now, "mmmm d, yyyy")
- 26 Format(Now, "ttttt")
- 27 Format the date type value
- 28 Format time as mmmm_yyyy
- 29 formatting the date and time with format function
- 30 h - Displays a number from 0 to 23 giving the hour.
- 31 Hh - Displays a number from 00 to 23 giving the two-digit hour.
- 32 m - Displays an integer from 1 to 12 giving the number of the month without a leading zero on single-digit months.
- 33 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.
- 34 mmm - Displays the month as a three-letter abbreviation (except for May) without a period.
- 35 mmmm - Displays the full name of the month.
- 36 N - Displays a number from 0 to 60 giving the minute.
- 37 Nn - Displays a number from 00 to 60 giving the two-digit minute.
- 38 oooo - Displays the full localized name of the month.
- 39 q - Displays a number from 1 to 4 giving the quarter of the year.
- 40 S - Displays a number from 0 to 60 giving the second.
- 41 Ss - Displays a number from 00 to 60 giving the two-digit second.
- 42 : - Time separator (typically a colon, but this depends on the locale).
- 43 ttttt - Displays the full time (hour, minute, and second) in the system"s default time format.
- 44 Using the Format Function to Format an Expression: Format(expression[, format[, firstdayofweek[, firstweekofyear]]])
- 45 w - Displays an integer from 1 (Sunday) to 7 (Monday) containing the day of the week.
- 46 ww - Displays an integer from 1 to 54 giving the number of the week in the year.
- 47 y - Displays an integer from 1 to 366 giving the day of the year.
- 48 yy - Displays a number from 00 to 99 giving the two-digit year.
- 49 yyyy - Displays a number from o iving the four-digit year.
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>