date & time

R

rob

Is there any formula to calculate dates and times. I would like to input a
start date then input an end date and have a formula that will calculate how
many days/weeks/months have passed (this would have to take into account leap
year). I would also like to do this for time. Input start time and end time
and calculate how many hours/minutes/seconds have passed. Finally input a
start time such as 1:00 pm add 45 minutes and have the answer be 1:45 pm.

Any help would be appreciated.
 
M

Myrna Larson

Dates are stored in Excel as the number of elapsed days since "Day 0", which
was arbitrarily chosen to be Dec 31, 1899. So the number 1 represents Jan 1,
1900, and the number 38614 represents Sep 19, 2005.

There is an undocumented function, DATEDIF, that will calculate elapsed time.
Assuming the earlier date in A1 and the later date in B1:

=DATEDIF(A1,B1,"y") for years
=DATEDIF(A1,B1, "ym") for months in the last partial year
=DATEDIF(A1,B1, "md" for days in the last partial month

For subtracting times, if the difference is less than 24 hours, the earlier
time is in B1 and the later time in A1,

=HOUR(B1-A1)
=MINUTE(B1-A1)
=SECOND(B1-A1)

If the clock crosses midnight between the start time and end time, then in
each of the above formulas, replace
B1-A1
with
B1-A1+B1<A1

If the difference could be > 24 hours, then the values in A1 and B1 must
include both the date and the time, and the formula for hours would be

=(INT(B1)-INT(A1))*24+HOUR(B1-A1)
 
Top