1. Home
  2. Creating a Custom Date/Time Format

Creating a Custom Date/Time Format

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.

Note: You can only create a single custom format. If you want to update your custom format this will update it in every spreadsheet where it is used.
Note: If you send your spreadsheet to a Genstat user who has set their own custom date format on a column, that user’s custom format will be displayed (overriding yours).

  1. 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 &&.

Note: If you use a 2 digit format for the year (i.e. yy rather than yyyy) to display a year outside the period 19XX – 20XX (where XX is set as a number between 0 and 99) then a full 4 digits for the year will be used automatically.

Component Description Example
&a am/pm am
&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
&m Month number 8
&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 ‘&’ &
Updated on February 28, 2019

Was this article helpful?