True. I should have mentioned that in my post. Excel dates are numbered
starting with 1/1/1900 (for Windows) which is day 1. 1/2/1900 is day 2 ...
7/21/2007 is day 39284.
So you can with caution subtract one date from another to obtain the number
of days difference. You must be careful to ensure that the date being
subtracted is equal to or less than the other date or you will get a
meaningless negative date which Excel will express as #'s. So if A1 has
7/1/2007 (day 39264) and A2 has 7/21/2007 (day 39284), =A2-A1 gives 20,
whereas =A1-A2 gives a negative date which is expresssed as #'s. Caution: if
the date contains a fraction which represents the time of day (0.25, 6am;
0.5, noon; 0.699305556, 4:47pm; 0.75, 6pm) that will be taken into account
if you simply subtract the dates. 1/20/2007 3:00 am (39102.125) minus
1/19/2007 5:00am (39101.20833) results in 0.916667 which will be expressed
as (1/0/1900 22:00). That is: 0 days, 22 hours.

I don't mean this to be a discussion of dates. Put simply, to subtract dates
and avoid the time fraction use a function like TRUNC. If cell A1 contains
1/20/2007 3:00 am (39102.125)and cell B1 contains 1/19/2007 5:00 am
(39101.20833) then =TRUNC(A1) - TRUNC(B1) results in 1 which is probably
what you want. You could also use other functions such as ROUNDUP and
ROUNDDOWN to handle the fractional parts, depending on if you want
fractional parts to be counted as whole days or not. It is not always
obvious what a "date" cell contains. If the cell contains both a date and
time and is formatted as a date only, you will not see the time portion in
the cell. If it is 7/21/2007 at 9:37am exactly and cell A1 contains =NOW(),
A1 will be 39284.400694. If cell B1 contains =TODAY(), B1 will be 39284. If
both are formatted as m/d/yyyy you'll see 7/21/2007 for both. The two appear
to be the same. But if you subtract B1 from A1 you'll get 0.400694 when you
might be expecting 0.

So, to be on the safe side, if you want the difference in whole days between
two dates, make sure the date you subtract is equal to or less than the
other date and use a function such as TRUNC, ROUNDUP, ROUNDDOWN or DATEDIF.

I'm sure there are great treatises on Excel dates out there somewhere.
Perhaps someone could post some date information sites.

"Stephane Quenson" wrote in
message ...
You can also simply type =A2-A1, as a date is a number with the integer
part
representing a number of days from a reference date, and the fractional
part
being the time or a fraction of a day (e.g. 0.25 is 06:00 am).