Thursday, May 04, 2006

Gregorian Dates in Excel

All dates and time is stored in Excel as an integer and declimal places respectively. The dates start with 1 as the first day of 1900 or 1904 depending on which you choose.
Time is represented as a decimal represenation of the time on a 24 hour clock. Eg Midday is represented by 0.5, 6AM as 0.25 and 6PM as 0.75 etc.

The date thing can be all rather confusing thanlkts to Julis Cesear and Pope Gregory XIII. Don't believe me- thean read on.

Doug Klippert from Unoffical Office Stuff explains:

The year -45 has been called the "year of confusion," because in that year Julius Caesar inserted 90 days to bring the months of the Roman calendar back to their traditional place with respect to the seasons. This was Caesar's first step in replacing a calendar that had gone badly awry. Caesar created a solar calendar with twelve months of fixed lengths and a provision for an intercalary day to be added every fourth year. As a result, the average length of the Julian calendar year was 365.25 days.

The Gregorian (Pope Gregory XIII) calendar is based on a cycle of 400 years, which comprises 146,097 days. Since 146,097 is evenly divisible by 7. Dividing 146,097 by 400 yields an average length of 365.2425 days per calendar year, which is a close approximation to the length of the tropical year. The Gregorian calendar accumulates an error of one day in about 2500 years.


Theres more there than that of course, ad a cursory Google search on "Excel time 1904 Gregorian" will return lots of articles on the subject.

So if you are confused by dates, than don't be. Just remember that there is less than 366 days to your next birthday and you are looking good!


Posted by: Jethro | 12:24 AM |