1. Home
  2. Setting a Default Date/Time Format

Setting a Default Date/Time Format

A default date is applied to any columns imported from an external source. For example, if you receive several spreadsheets from different users, each of whom has used their own date format, Genstat will display all dates using your default settings when you open the spreadsheets. After import you can still change the date format for individual columns within a spreadsheet.

  1. From the menu select Tools | Options, then click the Date Format tab.

  2. Select from the standard default formats or create a custom format (see Creating a Custom Date/Time Format).
  3. You can adapt the default format further by setting the language used to display dates, and editing the individual month or day entries.
  4. Click OK to apply your settings.

When you next open a spreadsheet the dates will display using your defaults.

Options

Default date format This is a list of predefined date and time formats that can be used to display numeric date or time values.
Custom date format This lets you construct your own format. See Creating a Custom Date/Time Format.
Start dates from

This lets you specify the base date that all other dates are to be numbered from.

  • 01/03/1600 – the nominal starting day for the Gregorian calendar
  • 10/01/1900 – the starting day used by spreadsheets such as Excel, Lotus and Quattro
Month and day lists Contains lists of the months and day. When you click a list the List entries will display to the right. You can edit the individual list entries by clicking Edit list.
List entries Displays the entries contained within your selected Month and day list. You can copy the list entries to the clipboard by right-clicking and selecting Copy.
Set 2 digit years less than x as 2000s

Controls how years specified by two digits are to be interpreted; whether the first two digits are assumed to be either 19 or 20. The value given here specifies the cut-off date: dates less than this value represent years beginning 20, and two digit dates greater than or equal to this value will represent years beginning 19.

For example, if 2 digit years is set to 30, years in the range 00 – 29 will represent the years 2000 – 2029, and years in the range 30 – 99 represent the years 1930 – 1999.

The setting 0 will ensure that all two-digit dates belong to the 1900s and the setting 100 will mean that they all belong to the 2000s.

A related problem is the representation of years not in the ranges implied by the 2 digit years setting. Using the example above, a date such as 1920 would have to be displayed using 4 digits even if 2 digits had been requested.

For unambiguous interpretation of output, we recommend you use 4 digits for displaying years wherever practical.

Use language defaults for Lets you specify control the default language used for the month and day names. When a language is selected the names are automatically changed in the Month and Day lists. You can then edit the month names if you want to change these from the default values for the language.
Displays times without the seconds Controls whether the seconds are included when displaying times. For example, if selected the time will be displayed as HH:MM (i.e. 3:24), otherwise it will be displayed as HH:MM:SS (i.e. 3:24:33).
Excel times less than XX days will not be rebased to 1600

When data from Excel in time format does not have a day component (e.g. 0.5 = 12:00), a value of 109513 is added to the column values. When the maximum time in a column is less that the specified number of days a column will not have 109513 added to the column values. The value 109513 is usually added to Excel dates to change the day number from the Excel base date of 1-Jan-1900 to the Genstat server base date of 1-March-1600.

These values still display correctly as times, but if they are displayed using a date format they will be displayed as dates from March 1600, rather than January 1900. Note that this option only applies to columns which are in time format (DREPRESENTATION values 36-39, and custom formats only containing hour/minute/second place holders).

Day used for mmm-yy date format

This specifies the day number that is used for a date format where the day is not known. For example, if in a spreadsheet you enter the date Dec-08, the day is not known. For these date formats an underlying date value must be assigned a distinct value for internal storage and calculations.

To do this a particular day of the month is assigned for the month and year. You can choose either the Start of Month (1), Mid Month (14,15) or End of Month (28-31) to use as the day to represent the month and year. So for example Dec-08 would have a date value of 1-Dec-2008, or 15-Dec-2008 or 31-Dec-2008 for the start of month, mid month and end of month respectively.

Updated on April 2, 2019

Was this article helpful?