
Almost all projects and applications in Excel use
dates and times in some way. A project may need to determine the
number of days between two dates, or determine the date that is so many days
in the future. Timesheet projects need to determine the elapsed time
between and start and end times, or determine how many hours are
overtime.
Before you can take advantage of some of the more
power date and time formulas, you have to understand how Excel stores dates
and times. Regardless of how you have formatted a cell to display a
date or time, Excel always internally stores dates And times the same
way.



How Excel Stores Dates And Times
Excel stores dates and times as a number
representing the number of days since 1900Jan0, plus a fractional
portion of a 24 hour day: ddddd.tttttt
. This is called a serial date, or serial
datetime.
Dates
The integer portion of the number, ddddd,
represents the number of days since 1900Jan0. For example, the date
19Jan2000 is stored as 36,544, since 36,544 days have passed since
1900Jan0. The number 1 represents 1900Jan1. It should be
noted that the number 0 does not represent 1899Dec31. It does
not. If you use the MONTH
function with the date 0, it will return January, not December.
Moreover, the YEAR
function will return 1900, not 1899.
Actually, this number is one greater than the
actual number of days. This is because Excel behaves as if the date
1900Feb29 existed. It did not. The year 1900 was not a leap
year (the year 2000 is a leap year). In Excel, the day after
1900Feb28 is 1900Feb29. In reality, the day after 1900Feb28 was
1900Mar1 . This is not a "bug". Indeed, it is by
design. Excel works this way because it was truly a bug in Lotus
123. When Excel was introduced, 123 has nearly the entire market for
spreadsheet software. Microsoft decided to continue Lotus' bug, in
order to fully compatible. Users who switched from 123 to Excel would
not have to make any changes to their data. As long as all your
dates later than 1900Mar1, this should be of no concern.
Times
The fractional portion of the number, ttttt,
represents the fractional portion of a 24 hour day. For example, 6:00
AM is stored as 0.25, or 25% of a 24 hour day. Similarly, 6PM is stored at
0.75, or 75% percent of a 24 hour day. As
you can see, any date and time can be stored as the sum of the date and the
time. For example, 3PM on 19Jan2000 is stored internally as
36544.625. When you enter a time without a value, such as entering 15:00
into a cell, the date portion is a zero. The zero indicates that there is
no date associated with the time. You should remember that entering just a
time does not automatically put in the current date.

