lotus

previous page: 7.5.1 Science and Engineering (spreadsheets)
  
page up: Spreadsheets FAQ
  
next page: 8.0 1-2-3 history (spreadsheets: Lotus 1-2-3)

7.6 How do I deal with dates and times in my spreadsheet?




Description

This article is from the Spreadsheets FAQ, by Russell Schulz casfaq@locutus.ofB.ORG with numerous contributions by others.

7.6 How do I deal with dates and times in my spreadsheet?

this depends on your spreadsheet.

the most common method is to ignore dates entirely -- just
represent the dates as numbers, and use them like any other numbers.

in particular, every number is treated as the number of whole
days starting with some particular date long ago (it is called
the epoch, and usually has a value of 1). so a value of 2 would
be the day after the epoch, 3.5 would be noon (12 hours=0.5 days)
2 days after the epoch, and 12.25 would be 6 am (6 hours=0.25 days)
11 days after the epoch.

(Unix keeps its time in a similar way, except it only uses integers;
it counts the number of seconds since the start of 1970. this is
why many Unix systems are in danger of severe clock problems in 2038 --
the signed 32-bit counter will overflow.)

when you format the numbers as dates or times, a lot of things just
magically work. for instance, you can add 7 to a date, and it will
always be a week later, even if it wrapped around a month or year or
century -- it's the formatting that does all the hard work of deciding
what date (and time) a given number actually represents.

there are two big problems:

1. there was no February 29 in 1900:

by the 4/100/400 rule, 1900 was not a leap year (and 2000 was).
but for backward compatibility with an error in an early
spreadsheet that used the epoch of 1900/Jan/01, many spreadsheets
treat 1900 as if it was (and claim that `60' should be treated as
the nonexistent 1900/Feb/29). this means any date calculations
that use dates before 1900/Mar/01 are suspect.

2. the epoch can change:

perhaps to avoid that problem, the Macintosh system has used
an epoch of 1904/Jan/01. but since the dates are stored only
as numbers, you can suddenly find your dates shifting by about
4 years when opening spreadsheets on different computers
(notably, Microsoft Excel allows you to switch between the
`1900 date system' and the `1904 date system' interpretations
on the same computer).

there are 3 small problems:

1. the difference between two dates can't be formatted as a date:

if you want to show the difference between two dates (e.g., age)
you simply subtract the two numbers -- but do NOT format them
as dates (since a difference of 3 days might show up as the
confusing 1900/Jan/04). always treat (and format) the difference
as a number of days, possibly dividing by

7 to show the number of weeks;
365[.2422...] to show an approximate number of years;
30[.416...] to show an approximate number of months.

2. times over 24 hours may be hard to format:

if you add 18 hours (0.75) and 12 hours (0.5), you may not get
the 30 hours (1.25) you expect, but 1 day with 6 hours (0.25)
left over. you may need to format the result as a number, in days,
or multiply by 24 to show the time in hours. many newer versions
have a special `overflow' format to treat a number as time only,
without using the integer part as a day count.

3. two digits for the year were never enough (past 1900, anyway):

if you enter data with 2-digit years, shame on you. but if you
insist, your software may be trying to outsmart you -- some packages
treat any two-digit year from 00-29 as 2000-2029. if this happens,
and you format the date with a 2-digit year (shame on you!), you may
never notice this switch until the day you accidentally discover that
your `average age' calculation is just a little too low, because of a
few negative ages you used!

 

Continue to:













TOP
previous page: 7.5.1 Science and Engineering (spreadsheets)
  
page up: Spreadsheets FAQ
  
next page: 8.0 1-2-3 history (spreadsheets: Lotus 1-2-3)