You can construct your own date format using a combination of text and place holders (& + a letter) for various time and date components. This format will then be available to all spreadsheets.
- Type your format directly into the Custom date format field using the time/date components shown in the table below, then click OK twice.
For example, to create the date format ‘Wednesday January 1 2017‘, type &w &N &d &Y.
To create the time format ‘12:48:31‘ type &O:&U:&S.
Use can use spaces, colons or other characters as separators, which will all be displayed in your output. If you want an ampersand & as a placeholder you must use a double ampersand &&.
|&C||Century as a 4 digit number||2000|
|&c||Century as a 2 digit number||20|
|&D||Day of month as 2 digits (i.e. leading 0 for day < 10)||07|
|&d||Day of month||7|
|&E||Elapsed time in seconds to 2 decimal places||278.35|
|&e||Elapsed time in hours to 2 decimal places||3.45|
|&H||Hour in the day with leading zero for hour < 10||06|
|&h||Hour in the day||6|
|&M||Month number with leading zero for month < 10||08|
|&N||Month name in full||January|
|&n||Month name abbreviated||Jan|
|&O||Hour in the half day (12 hrs) with leading zero for hour <10||04|
|04||Hour in the half day (12 hrs)||4|
|&S||Seconds in the minute as 2 digits||03|
|&s||Seconds in the minute||3|
|&T||100ths of seconds in the second as 2 digits||02|
|&t||100ths of seconds in the second||2|
|&U||Minutes in the hour as 2 digits||09|
|&u||Minutes in the hour||9|
|&W||Day of week as a number 1-7; e.g. 1 = Monday, 2 = Tuesday, etc||4|
|&w||Day of week||Sunday|
|&Y||Year with 4 digits||2018|
|&y||Year as 2 digits||18|
|&&||Displays an ampersand ‘&’||&|