FORMAT |
FORMAT(expression [,format] [,firstdayofweek] [,firstweekofyear]) |
Returns the text string of a number or date in a particular format (Variant / String). |
expression | The 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