Date Formats

Within a spreadsheet a number within a variate or factor column can be interpreted as a time value and displayed using a date/time format. Genstat stores date/time values as 8 byte double precision real values where the integer part of the value represents the number of days from a base date, and the fractional component is the time during the day as a fraction of 24 hours (e.g. 0.25 = 6 hours). Using this representation, the time between two dates can simply be calculated by subtraction of the two underlying day numbers. Also this format makes Genstat Year 2000 compliant.

Start dates from

The base day that all other dates are numbered from.

1/3/1600	The nominal starting day for the Gregorian calendar.
1/1/1900	The starting day used by spreadsheets such as Excel, Lotus and Quattro.

Format list

A dropdown list of a wide range of predefined date and time formats that can be used to display the numeric date/time values. The list includes an item called Custom that can be used to specify custom date format.

More options for custom date formats can be found on the Date Format tab within the Tools | Options menu. The default date format applied to a column is set using the Date Format tab opened from the Tools | Options menu.

The predefined date formats are listed in the table below.

No.	Format	Example
1	dd/mm/yy	03/08/98
2	dd/mm/yyyy	03/08/1998
3	d/m/yy	3/8/98
4	d/m/yyyy	3/8/1998
5	ddmmyy	030898
6	ddmmyyyy	03081998
7	ddmmmyy	03Aug98
8	ddmmmyyyy	03Aug1998
9	dd-mmm-yy	03-Aug-98
10	dd-mmm-yyyy	03-Aug-1998
11	dmmmyy	3Aug98
12	dmmmyyyy	3Aug1998
13	d-mmm-yy	3-Aug-98
14	d-mmm-yyyy	3-Aug-1998
15	d-mmmm-yy	3-August-98
16	d-mmmm-yyyy	3-August-1998
17	yymmdd	980803
18	yyyymmdd	19980803
19	yy/mm/dd	98/08/03
20	yyyy/mm/dd	1998/08/03
21	mmddyy	080398
22	mmddyyyy	08031998
23	mm/dd/yy	08/03/98
24	mm/dd/yyyy	08/03/1998
25	mmm-dd-yy	Aug-03-98
26	mmm-dd-yyyy	Aug-03-1998
27	yyyy-mm-dd	1998-08-03
28	weekday, dth mmmm yyyy	Monday, 3 of August 1998
29	weekday	Monday
30	mmm-yy	Aug-98
31	yy	98
32	yyyy	1998
33	dd-mmm-yyyy time	03-Aug-1998 18:55:30.35
34	yyyy-mm-dd time24 (ODBC Std)	1998-08-03 18:55:30
35	dd-mmm-yyyy time12	03-Aug-1998 6:55:30 pm
36	time24	18:55:30
37	time12	6:55:30 pm
38	hours	48:55:30
39	seconds	68538.35
40	numeric date-time	03/08/1998 18:55:30
41	m-yy	8-98
42	m-yyyy	8-1998
43	mm-yy	08-98
44	mm-yyyy	08-1998
45	d/m	3/8
46	dd/mm	03/08
47	d-mmm	3-Aug
48	dd-mmm	03-Aug
49	mmm	Aug
50	Custom spreadsheet date format	(See below)
51	Custom server date format	(See below)

The first numbers in the table are the equivalent values of DREPRESENTATION parameter in PRINT, except numbers 50 and 51 which correspond to setting DREPRESENTATION to a text.

Custom date format

Lets you specify a Custom spreadsheet date format which is constructed by as a combination of any text, and place holders (& + a letter) for various time and date components. You can select one of the more common place holders by clicking on the <- button and selecting the item you want to insert into the text. Use an ‘&& if you want an ‘&’ to appear in the text. All the available time/date components are shown in the following table:

&A	AM/PM.	AM
&a	am/pm.	am
&C	Century as a 4 digit number	2000
&c	Century as a 2 digit number	20
&D	Day of month 2 digit (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	98
&h	Hour in the day	6
&M	Month number with leading 0 if month < 10	08
&m	Month number	8
&N	Month name in full	January
&n	Abbreviated month name	Jan
&O	Hour in the half day with leading zero for hour < 10	04
&o	Hour in the half day i.e. 1-12	4
&S	Seconds in the minute as two digits	03
&s	Seconds in the minute	3
&T	100ths of seconds in the second as two digits	02
&t	100ths of seconds in the second	2
&U	Minutes in the hour as two digits	09
&u	Minutes in the hour	9
&W	Day of week as a number 1-7; 1 = Monday etc.	4
&w	Day of week	Sunday
&Y	Year with 4 digits	1998
&y	Year as two digits	98
&&	Single & in text	&

There is only one custom format available for all spreadsheets, so that changing this in one column will change it in all other columns in all other spreadsheets, and if the spreadsheet is sent to another user, the custom date format displayed in a column will be that set up in their options. This format is more flexible than the server date format as it can have any separator text and a finer control of the date components.

Server date format

Lets you specify a Custom server date format which is constructed by as a combination of the various date/time component keywords and the separators space, minus, slash and comma ( -/,). Any other text will be ignored. You can select one of the more common place holders by clicking on the <- button and selecting the item you want to insert into the text. All the available time/date keywords are shown in the following table:

year as a two-digit number (omitting the century)

d	day number within the month, using the minimum number of digits	3
dd	day number within the month, using 2 digits	03
dth	day number with one digit and suffix	1st, 2nd, 3rd, 4th …
m	month number, using the minimum number of digits	8
mm	month number, using 2 digits	08
mmm	abbreviated month name	Aug
mmmm	month name in full	August
yy		98
yyyy	year as a four-digit number (including the century)	1998
weekday	day of the week	Monday
wday	abbreviated day of the week	Mon, Tue, Wed, Thur …
time24	time, including seconds, using a 24 hour clock	18:55:30
time12	time, including seconds, using a 12 hour clock (with a.m. and p.m.)	6:55:30 pm
time100	time, using 24 hour clock and including hundredths of seconds	18:55:30.35
hours	elapsed time in hours, minutes and seconds	48:55:30
hours100	elapsed time in hours, minutes, seconds and hundredths of seconds	48:55:30.35
minutes	elapsed time in minutes and seconds	1207:30
minutes100	elapsed time in minutes, seconds and hundredths of second	1207:30.35
seconds	elapsed time in seconds	68538
seconds100	elapsed time in seconds and hundredths of second	68538.35
h	hours in 24 hour format	9
hh	hours in 24 hour format with two digits	09
hhh	hours in 12 hour format	9
hhhh	hours in 12 hour format with two digits	09
u	minutes in the hour	5
uu	minutes in the hour with two digits	05
s	seconds in the minute	5
ss	seconds in the minute with two digits	05
a	am/pm indicator for 12 hour format	am

A separate custom server format can be set for each column, which is an advantage over the spreadsheet custom format. The server custom format can also use Custom spreadsheet date format keywords but you cannot mix the keywords, so that ‘wday &M-&Y’ would not be valid, but “&w &m-&y” would be ok.

Note

If a date format using just two digits for the year (i.e. contains yy rather than yyyy) is used to display a year outside the period 19XX – 20XX (where XX is set as a number between 0 and 99 by the Start dates from option on the Tools | Options | Date Format tab) page, then a full 4 digits for the year will be used automatically.