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

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

expressionThe valid expression.
format(Optional) A valid named format or user defined format expression (String):
"yyyy" = year
"q" = quarter
"m" = month
"M" = month (same as 'm')
"y" = day of year
"w" = weekday
"ww" = week
"h" = hour
"n" = minutes
"s" = second
"General Date" (uses short date + short time)
"Long Date"
"Medium Date"
"Short Date"
"Long Time"
"Medium Time" (uses 12 hr format)
"Short Time"
"General Number"
"@" 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

* If "format" is left blank, then it behaves exactly the same as the CSTR function.
* 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.
* To format numbers, use predefined named numeric formats or create user defined formats (eg ??).
* To format date and time serial numbers use date and time formats or numeric formats (eg ??).
* To format strings create your own user defined string formats (eg ??).
* 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 FORMAT$ function to return a String data type instead of a Variant/String data type.
* The equivalent Excel function is Application.WorksheetFunction.TEXT
* The equivalent .NET function is Microsoft.VisualBasic.Strings.Format
* For the Microsoft documentation refer to

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(), "Short Date") '= 10/10/2018
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(2002,1,1), "dddd dd/mm/yyyy") '= Tuesday 01/01/2002
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

Dim dtDate As Date
dtDate = #01/07/2011#
Debug.Print Format(dteDate, "ddd, mmm d, yyyy")
Debug.Print Format(dteDate, "mmm d, H:MM am/pm")

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

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