FORMAT

FORMAT(expression [,format] [,firstdayofweek] [,firstweekofyear])

Returns the text string of a number or date in a particular format (Variant / String).


expressionThe valid expression.
format(Optional) A valid named format or user defined format expression (String):
"yyyy" = year
"q" = quarter
"m","M" = month
"y" = day of year
"w" = weekday
"ww" = week
"h" = hour
"n" = minutes
"s" = second
"Long Date" (dd mmm yyyy)
"Medium Date" (dd-mmm-yy)
"Short Date" (dd/mm/yyyy)
"General Date" (Short Date + Short Time)
"Long Time" (hh:nn:ss)
"Medium Time" (hh:nn:AM/PM, 12 hr format)
"Short Time" (hh:nn)
"General Number"
"Currency"
"Fixed"
"Standard"
"Percent"
"Scientific"
"Yes/No"
"True/False"
"On/Off"
"@" string displayed character
">" string uppercase
firstdayofweek(Optional) A vbDayOfWeek constant that specifies the first day of the week (Integer):
0 = vbUseSystemDayofWeek
1 = vbSunday (default)
2 = vbMonday
3 = vbTuesday
4 = vbWednesday
5 = vbThursday
6 = vbFriday
7 = vbSaturday
firstweekofyear(Optional) A vbFirstWeekOfYear constant that specifies the first week of the year (Integer):
0 = vbUseSystem
1 = vbFirstJan1, the week containing Jan 1st (default)
2 = vbFirstFourDays, the first week containing 4 days
3 = vbFirstFullWeek, the first full week

REMARKS
* If "format" is left blank, then the default format used will depend on the type of expression (see below examples).
* The "format" argument can either be your own format or it can be one of the built-in named formats.
* If "expression" is a positive number and "format" is left blank, there is no leading space reserved for the sign. If you use the CStr() function then this leading space is retained.
* If you convert a Date directly to a string using CStr the resulting string will be formatted according to the "Short Date". There is one exception though any dates that fall outside the range 1930 - 1999 will always be formatted with four-digits for there year component regardless of the short date format.
* You can use the DATEADD function to returns a date with a specified time interval added.
* You can use the FORMATCURRENCY function to return an expression formatted as a currency value.
* You can use the FORMATDATETIME function to return an expression formatted as a date or time.
* You can use the FORMATNUMBER function to return an expression formatted as a number.
* You can use the FORMATPERCENT function to return an expression formatted as a percentage.
* You can use the FORMAT$ function to return a String data type instead of a Variant data type.
* You can use the NOW function to return the current system date and time.
* The equivalent Excel function is Application.WorksheetFunction.TEXT
* The equivalent .NET function is Microsoft.VisualBasic.Strings.Format
* link - learn.microsoft.com/en-us/office/troubleshoot/access/functions-return-wrong-week-number
* For the Microsoft documentation refer to learn.microsoft.com

Debug.Print Format("12.3456","0.00")                         '= 12.35  
Debug.Print Format("0.12","0.0000") '= 0.1200
Debug.Print Format("123.456","0.0000") '= 123.4560
Debug.Print Format(Now(), "hh:mm:ss AMPM") '= 08:42:31 AM
Debug.Print Format(Now(), "Long Date") '= 23 April 2023
Debug.Print Format(Now(), "Medium Date") '= 23-Apr-23
Debug.Print Format(Now(), "Short Date") '= 23/04/2023
Debug.Print Format(Now(), "General Date") '= 23/04/2023 12:56:48
Debug.Print Format(Now(), "Long Time") '= 12:56:04
Debug.Print Format(Now(), "Medium Time") '= 12:56 PM
Debug.Print Format(Now(), "Short Time") '= 12:56
Debug.Print Format(1234.56, "##,##0") '= 1,235
Debug.Print Format(1234.56, "£##,##0.00;(£##,##0.00)") '= £1,234.56
Debug.Print Format(-1234.56, "£##,##0.00;(£##,##0.00)") '= (£1,234.56)
Debug.Print Format(DateSerial(2023,1,1), "dddd dd/mm/yyyy") '= Tuesday 01/01/2023
Debug.Print Format(0.163,"Percent") '= 16.30%
Debug.Print Format(0.163,"Currency") '= £0.16
Debug.Print Format(0.163,"General Number") '= 0.163
Debug.Print Format("some text",">") '= SOME TEXT
Debug.Print Format("123456789","@@@") '= 123456789

'default value for "format"
Debug.Print Format(Now()) 'General Format
Debug.Print Format(1.1234567890123456789) '14 decimal places
Debug.Print Format("text") 'text

Dim dtDate As Date
dtDate = #01/07/2023#
Debug.Print Format(dtDate, "ddd, mmm d, yyyy") '= Sat, Jan 7, 2023
Debug.Print Format(dtDate, "mmm d, H:MM am/pm") '= Jan 7, 12:00 am

Debug.Print Format(20190208, "dd/mm/yyyy") = Overflow 'because 20190209 is not a valid date serial number

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top