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.
* 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

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