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 pre-defined 'Named Format' or a 'Custom Format' using date, time, number or string expressions (String): "General Date" (dd/mm/yyyy hh:nn) "Long Date" (dd mmm yyyy) "Medium Date" (dd-mmm-yy) "Short Date" (dd/mm/yyyy) "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 lowercase ">" string uppercase Valid Date Formats Valid Time Formats Valid Number Formats
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 be one of the predefined Named Formats, for example "Long Date" or it can be a custom format.
* Sunday is considered the first day of the week in: USA, Canada, South Africa, India, Japan, South Korea.
* Monday is considered the first day of the week in: UK, Germany, France, Australia, New Zealand, China.
* 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

'Default Formats
Debug.Print Format(Now())                                    'General Format  
Debug.Print Format(200)                                      '?  
Debug.Print Format(123.456)                                  '?  
Debug.Print Format(1.1234567890123456789)                    '14 decimal places  
Debug.Print Format("text")                                   'text  

'Predefined Named Formats
Debug.Print Format(Now(), "General Date")                    '= 23/04/2023 12:56:48  
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(), "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(0.163,"Percent")                          '= 16.30%  
Debug.Print Format(0.163,"Currency")                         '= £0.16  
Debug.Print Format(0.163,"General Number")                   '= 0.163  

'Custom Date/Time Formats
Debug.Print Format(Now(), "hh:mm:ss AMPM")                   '= 08:42:31 AM  
Debug.Print Format(DateSerial(2023,1,1), "dddd dd/mm/yyyy")  '= Tuesday 01/01/2023  

'Custom Number Formats
Debug.Print Format("12.3456","0.00")                         '= 12.35  
Debug.Print Format("0.12","0.0000")                          '= 0.1200  
Debug.Print Format("0.12","0.00%")                           '= ?  
Debug.Print Format("123.456","0.0000")                       '= 123.4560  
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)  

'Custom String Formats
Debug.Print Format("some text",">")                          '= SOME TEXT (uppercase)  
Debug.Print Format("SOME TEXT","<")                          '= some text (lowercase)  
Debug.Print Format("123456789","@@@")                        '= 123456789  

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