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"
"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 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 docs.microsoft.com

Format("12.3456","0.00") = 12.35 
Debug.Print Format("12.3456","0.00")

Format("0.12","0.0000") = 0.1200
Debug.Print Format("0.12","0.0000")

Format("123.456","0.0000") = 123.4560
Format(NOW(), "hh:mm:ss AMPM") = 08:42:31 AM
Format(NOW(), "Short Date") = 10/10/2018
Format(1234.56, "##,##0") = 1,235
Format(1234.56, "£##,##0.00;(£##,##0.00)") = £1,234.56
Format(-1234.56, "£##,##0.00;(£##,##0.00)") = (£1,234.56)
Format(DateSerial(2002,1,1), "dddd dd/mm/yyyy") = Tuesday 01/01/2002
Format(0.163,"Percent") = 16.30%
Format(0.163,"Currency") = £0.16
Format(0.163,"General Number") = 0.163
Format("some text",">") = SOME TEXT
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")

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