Dates formula

[davey]

I have 2 date columns. I would like a formula in a 3rd column to tell
me the differance in days of the two date columns

 

[Dave Thomas]

You can use the DATEDIF function.

This function from Lotus 1-2-3 is documented by Microsoft only in Excel
2002, but has been around since Excel 5

Its format is =DATEDIF(start_date,end_date,"F"). Note that start_date must
be less or equal to end_date or you will get a #NUM error.
F must be in quotes and may be the following:

"y" The number of complete years in the period
"m" The number of complete months in the period
"d" The number of days in the period.
"md" The difference between the days in start_date and
end_date. The months and years of the dates are ignored.
"ym" The difference between the months in start_date and
end_date. The days and years of the dates are ignored.
"yd" The difference between the days in start_date and
end_date. The years of the dates are ignored.

So if cell A1 has 01/01/2007 and cell B1 has 07/21/2007 then
=DATEDIF(A2,A1,"d") would return 201, the number of days difference between
those dates.

This function is also documented at:
http://www.cpearson.com/excel/datedif.htm

 

[Dave Thomas]

You can use the DATEDIF function.

This function from Lotus 1-2-3 is documented by Microsoft only in Excel
2002, but has been around since Excel 5

Its format is =DATEDIF(start_date,end_date,"F"). Note that start_date must
be less or equal to end_date or you will get a #NUM error.
F must be in quotes and may be the following:

"y" The number of complete years in the period
"m" The number of complete months in the period
"d" The number of days in the period.
"md" The difference between the days in start_date and
end_date. The months and years of the dates are ignored.
"ym" The difference between the months in start_date and
end_date. The days and years of the dates are ignored.
"yd" The difference between the days in start_date and
end_date. The years of the dates are ignored.

So if cell A1 has 01/01/2007 and cell B1 has 07/21/2007 then
=DATEDIF(A1,B1,"d") would return 201, the number of days difference between
those dates.

This function is also documented at:
http://www.cpearson.com/excel/datedif.htm

 

[Guest]

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).

 

[Dave Thomas]

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.

 

[Rick Rothstein \(MVP - VB\)]

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

Or, you can use the ABS (absolute value) function on the difference.

Rick

 

[Dave Thomas]

I realize that. But usually one date is supposed to be less than or equal to
the other. So I prefer to get a negative result so I'll see the #'s to let
me know something is wrong.
E.g. Today is 7/21/2007 and someone's hire date in A1 is 7/1/2008, so
abs(TODAY() - A1) gives 346 days. I'd rather see the #'s. In obtaining data,
manual entry or otherwise,
things do slip through the cracks.

Regards,

Dave