Calculating in Yr,mo,days

R

Robert McN

Is there a formula such that one could enter a date and have the results be expressed in the time that has elapsed from a designated time,in years, months, and days? So say, for example, the base date is Jan. 1, 2002, if one entered 8/8/04 the result would be expressed as 2 years, eight months, eight days.
 
J

JulieD

Hi Robert

can get 2 years, 7 months and 7 days using the datedif function as below:
=CONCATENATE(DATEDIF(A1,B1,"y"), " years, ", DATEDIF(A1,B1,"ym"), " months,
", DATEDIF(A1,B1,"md"), " days")
where A1 contains 1/1/04 and B1 contains 8/8/04

will that do?

Cheers
JulieD



Robert McN said:
Is there a formula such that one could enter a date and have the results
Click to expand...
be expressed in the time that has elapsed from a designated time,in years,
months, and days? So say, for example, the base date is Jan. 1, 2002, if
one entered 8/8/04 the result would be expressed as 2 years, eight months,
eight days.
 
J

JE McGimpsey

You need to know that you can get some bizarre answers using
DATEDIF(...,"md"). For instance

A1: 1/31/2003
B1: 3/1/2004

returns

1 years, 1 months, -1 days
 
J

JulieD

Hi Jim

oh, didn't realise that ... all the dates i plugged into formula seemed to
work fine .. is there any alternative functions?

Cheers
JulieD
 
J

JE McGimpsey

Not really - you can search the archives for posts by Norman Harker,
who's taken an interest in laying out the options, and provides clear
guidance.

The problem is the fuzzy definition of "month". For January, a month is
clearly 31 days. For February, a month can be 28 or 29 days. But when
you say "one month from 31 January", are you talking about 31 days?
28/29 days? Any number of days that give the end of the month?
 
Top