NSW Schools

Printer Version of Page


Dates, Time & Computing

  • Dates, Time & Computing
  • Preliminary Reading
    • Day Time
    • The Year
    • What was the y2k problem
    • Preliminary Exercise: Calculating Days of the Week
  • Exercises / Calculations
    • Excel Dates
      • Day Numbers
      • January 0 1900 and Date Overflows
    • WWII Date Count
    • Unix Dates
    • Date Representations in Different Countries
    • References

Preliminary Reading

Defining a year or even a day is not something that can be done easily. It was not until 1 October 1949, when Mao Zedong declared that China would follow the Gregorian calendar, that the entire world agreed what the date was for the very first time.

We work with dates everyday without giving it a second moments consideration but dates have always presented challenges for computing. Powerful tools are required in order to manipulate dates, for example:

  • How can a computer determine the current date and time?
  • Given that computers must store dates using finite numbers when will they run out of dates?
  • How can we test dates for validity (eg how can a booking system determine whether or not Friday May 28 2004 a valid date)?
  • What will the day of the week be 894 days from today?
  • How can we write a calendar script to support a diary application package?
  • How many days elapse between two dates (you may need this to calculate average consumption per day based on a time interval say 30 March 2001 to 28 December 2002)?
  • Date errors or mistakes can disrupt interest rates, funds transfers, access and control systems, telephone systems, electric power generation and navigation.

Day Time

The practical measurement of time formerly depended on determining the period of rotation of the earth relative to the astronomical objects (most notably our sun). Today, atomic clocks provide a uniform time-scale which can be used as the basis for coordinated universal time (UTC).

The mean solar day, or average period of time taken by the earth to make one complete spin on its axis, has been determined as 24 hours 3 minutes and 56.5 seconds. The mean solar day is used to define solar time.

The day according to civil time begins at midnight. Periodically civil time needs to be adjusted by one-second increments to ensure consistency with the earth’s rotational time scale remains within 0.9 seconds. However, civil time is usually not used, since it depends on the observer’s longitude.

Standard time, which is the same throughout a given time zone, is generally adopted. Under this scheme the earth is divided into 24 time zones, each of which is about 15° of longitude wide and corresponds to one hour of time. Within a zone all civil clocks are set to the same local solar time.

The Year

The solar or tropical year is the time between successive points in time at which the sun appears to cross the equator from south to north pole. It has been determined as 365 days, 5 hr, 48 min, 46 sec of mean solar time.

The sidereal year, is the time required for the earth to complete one revolution of the sun as measured relative to the stars. The sidereal year is approximately 365 days, 6 hr, 8 min, of mean solar time and is therefore longer than the tropical year by about 20 minutes.

The calendar year now always contains a whole number of days, the ordinary year being 365 days and the leap year 366 days.

What was the y2k problem

Back in the 1970s and 1980s computers had far less power than they do today and programs were written to use as little memory as possible. Consequently many programs that stored dates just stored years using two digits. 1985 would have been stored as 85 and 1996 would have been stored as simply 96. At the turn of the century it was anticipated that this could cause many problems since the programs would literally run out of dates and they would either crash or simply reset themselves at year 00. As a direct consequence of this fact many millions of programs and microchips had to be checked to see how they would cope with a change of century. This was a very expensive and time consuming exercise.

"For example, to calculate how old you are a program will take today's date and subtract your birthdate from it. That subtraction works fine on two-digit year dates until today's date and your birthdate are in different centuries. Then the calculation no longer works. For example, if the program thinks that today's date is 1/1/00 and your birthday is 1/1/65, then it may calculate that you are -65 years old rather than 35 years old. As a result, date calculations give erroneous output and software crashes or produces the wrong results." (

Preliminary Exercise: Calculating Days of the Week

  • The second row of the table shows the day number corresponding to each day of the week.
  • The third row shows the remainder when the number of days from the day number is divided by 7 (written Day # mod 7).
  • Assigning Sunday as the starting day (Day 1). The fourth and sixth rows show the number of days elapsed from the current day
  • Fill out the fifth and seventh rows to show the remainder when the number of days from the Sunday starting day is divided by 7.
Day #1234567
Day # mod
Day #891011121314
Day # mod 7       
Day #15161718192021
Day # mod 7       

If the first day of the year is Sunday what day of the week will the following days be

  • the 96th day of the year
  • the 114th day of the year
  • the 228th day of the year

If today is Friday (day 1) what day of the week it will be in

  • 96 days time
  • 114 days time
  • 365 days time
  • 366 days time

Birthdays and leap years

  • If your birthday was on Tuesday in 2002 what day will it be on in 2003?
  • If your birthday was on Tuesday January 13 in 1995 what day would have it been on in 1996?
  • If your birthday was on Tuesday March 13 in 1995 what day would have it been on in 1996?

Exercises / Calculations

Exercise 1

Visit and

  • Enter your birth date and determine how many days you have been alive for?
  • Enter today's date and determine what date it will be in 381 days?

Excel Dates

Day Numbers

Exercise 2

  • Open up a Microsoft Excel Spread Sheet and enter the date 04/08/1999 (4 August 1999).
  • Now choose Format>Cells> and change the number type from date to General
  • You should notice (on a machine using Australian Dates) that the date is converted to the number 36376. If the machine uses American dates then it may display 36258 corresponding to (8 April 1999)
  • Now increase that number by one and convert the format of the cell back to the same date format shown above.
  • Format>Cells>Date
  • Notice that the date has rolled over to the next day or the day after 04/08/1999.

January 0 1900 and Date Overflows

Exercise 3

  • Start with a new Excel Cell Enter the Number 0 then choose Format>Cells>Date. What date does this give
  • Start with a new Excel Cell Enter the Number -245 then choose Format>Cells>Number>Date. What date does this give (it should give an error ####)
  • What is day zero in your Microsoft excel spread sheet
  • What happens if you try to enter the date 1 March 1862 into your Microsoft Spreadsheet. Can you format it as a date of the format 1 March 1862?
  • What is the Biggest Date Number and corresponding Date that your Microsoft Excel will accept? (you will probably find that biggest date it will accept is much less than 12,000 years from now).
Graphic showing day numbers and dates in Microsoft Excel

WWII Date Count

Exercise 4

According to the BBC internet site World War Two in Europe began on 3 September 1939 when Britain and France declared war on Germany. World War Two can be deemed to have ended when Emperor Hirohito made his first ever broadcast to the Japanese people on 15 August 1945. Hirohito remarked that the war did not turn in Japan's favour.

  • According to Microsoft Excel what day number is 3 September 1939
  • According to Microsoft Excel what day number is 15 August 1945
  • What is the difference between these two day numbers
  • Use Excel to subtract these two dates How many days elapsed between 3 September 1939 and 15 August 1945. Experiment with the formatting of what you get out.
  • Go Back to and execute a query to determine how many days WW2 lasted for.
  • Do your excel and results agree (they should)

Unix Dates

Unix figures out all its dates by counting the number of seconds since midnight 1 January 1970. The Unix time stamp will report the number of seconds since this time, which is when time began for Unix.

Exercise 5

  • How many seconds do you have to add to the Unix time stamp to add exactly one extra day?
  • How many seconds do you have to add to the Unix time stamp to add exactly one extra week?

Unix Date Anomalies

All computer systems have date anomalies. Unix is expected to experience date problems in the year 2038.

Exercise 6

Do a calculation to estimate approximately how many seconds there are in the Unix life-time from 1 January 1970 to 1 January 2038 (ignore leap years)?

In the year 2038 the built-in variable used to keep time in Unix and similar operating systems will overflow. The maximum number of seconds that the Unix time variable can count is 2,147,483,647. This number of seconds will be reached on 19 January 2038 at 03:14:07.

Exercise 7

What power of 2 is 2,147,483,647 closest to (hint use your calculator and divide log 2,147,483,647 by log 2)?


The time stamp variable is 32 bits long. Only 31 of these bits are used to forward store the number of seconds. The maximum number of seconds the remaining 31 bits can accommodate is therefore:

230 + 229 + 228 + 227 ... + 23 + 22 + 21 + 20
230 + 230 – 1
2 * (230) – 1
231 – 1

Note: in the above calculation we have 31 bits but since we start at 20 we get to only 230

Exercise 8

Evaluate 231 – 1 using your calculator and determine that it is in-fact exactly the number of seconds in the Unix Lifetime (2,147,483,647)?

A solution to the Unix Date Problem

It has been suggested that the Unix date problem can be fixed by converting the Unix time stamp variable from a 32 bit variable to a 64 bit variable. In which case 63 bits would be used to forward count the number of seconds in the Unix lifetime.

Exercise 9

What is the maximum number of seconds that a 64 bit time stamp variable can accommodate before it overflows?

Ignoring leap years approximately how many years would be available in a 64 bit Unix lifetime?

Date Representations in Different Countries

In England and Australia dates are written in the format day/month/year, however in the United States, the format "month, day, year" is used.

A date in Australia of 04/08/1999 would be the fourth of August 1999. In the United States the same date would represent the eighth day of April 1999.

There is clearly large potential for dates to be misinterpreted when crossing national borders. Consider a Microsoft excel spreadsheets which contains a date written in Australia of 04/08/1999. If the computer was set up for Australia then the date would be fine but if the file is e-mailed to a computer reading US dates problems could result. This problem is resolved by actually storing dates in spreadsheets and other applications directly in terms of pure numbers. That way whenever a computer opens a file it just calculates the date based on the number in the date field.

A computer using United states date settings would present an Australian date correctly with respect to its date formatting since it would actually calculate what date to use based on the numerical value of the date field.


The Calendar. DE Duncan. Fourth Estate - London 1998.

The Penguin Dictionary of Physics. VH Pitt (Editor). Penguin 1987.

How Stuff Works

The Next Millennium Headache