date / time difference

[SS]

in A1 previous date
in A2 todays date
in A3 i have formula.... =DATEDIF(A1,A2,"d")

Not working, how do I get it to show the length of time between the 2
dates. Years and months

I have cells formulated to dates

Can anyone sort for me
thanks

 

[Gord Dibben]

What is "not working"?

Error or ???

Works fine for me with real dates. Are the values valid dates?

=DATEDIF(A1,A2,"y") & " years, " & DATEDIF(A1,A2,"ym") & " months, "&
DATEDIF(A1,A2,"md") & " days"


Gord Dibben MS Excel MVP

 

[joeu2004]

in A1 previous date
in A2 todays date
in A3 i have formula.... =DATEDIF(A1,A2,"d")

Not working, how do I get it to show the length of time between the 2
dates. Years and months[.] I have cells formulated to dates

Define "not working". Provide example dates and show what that
DATEDIF returns. I presume it returns a __correct__ number of days;
the only thing "not working" about it is: you want years and months.
Right?

But in what form do you want the result? In seperate columns? In
text?

For example, ostensibly:

=DATEDIF(A1,A2,"y") & " years " & DATEDIF(A1,A1,"ym") & " months"

However, note that if A1 is 2/29/1980 and A2 is 2/28/2010, that
returns "29 years 1 month". Most people would want "30 years 0
months". Note that EDATE(A1,30*12) is 2/28/2010.

This is a special was "previous date" is on Feb 29 of a leap year, and
"today's date" is on Feb 28 of a non-leap year.

The following can be used to make the appropriate adjustment:

=IF(EDATE(A1,12*DATEDIF(A1,B1,"y")+12)=B1,
DATEDIF(A1,B1,"y")+1 & " years 0 months",
DATEDIF(A1,B1,"y") & " years " & DATEDIF(A1,B1,"ym") & " months")

PS: For broader participation, you might want to post future
inquiries using the MS Answers Forums at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel.
It's not that I like that forum. It's just that MS has ceased to
support the Usenet newsgroups. Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.

 

[joeu2004]

Errata for typos and copy-and-paste errors....

=DATEDIF(A1,A2,"y") & " years " & DATEDIF(A1,A1,"ym") & " months"

=DATEDIF(A1,A2,"y") & " years " & DATEDIF(A1,A2,"ym") & " months"

=IF(EDATE(A1,12*DATEDIF(A1,B1,"y")+12)=B1,
DATEDIF(A1,B1,"y")+1 & " years 0 months",
DATEDIF(A1,B1,"y") & " years " & DATEDIF(A1,B1,"ym") & " months")

=IF(EDATE(A1,12*DATEDIF(A1,A2,"y")+12)=A2,
DATEDIF(A1,A2,"y")+1 & " years 0 months",
DATEDIF(A1,A2,"y") & " years " & DATEDIF(A1,A2,"ym") & " months")

 

[SS]

It returns I get #NUM! as the answer in cell

What formatt should the cells be
A1 and A" are dates
A3 also dates

 

[joeu2004]

It returns I get   #NUM! as the answer in cell

Usually that means that the first argument is greater than (later date
than) the second argument.

But since you neglect to post your formula and actual data (what is
"it"?), there is little more that I can offer.

Be sure to see my response regarding the handling of the leap day, Feb
29.

What formatt should the cells be
A1 and A" are dates

They should be dates -- ideally numeric dates, but DATEDIF seems to
tolerate text dates. If DATEDIF does not recognize text as dates, it
returns #VALUE, not #NUM.

A3 also dates

Huh?! Previously, you said that A3 contains the DATEDIF formula.
DATEDIF returns a number of days, years, months, not a date.

 

[joeu2004]

They should be dates -- ideally numeric dates, but DATEDIF seems to
tolerate text dates.

The point I'm trying to make is: TYPE(A1) and TYPE(A2) should both be
1, not 2.

Normally, when you enter dates in a form that Excel recognizes as
such, it will be stored as a number which Excel usually displays in
the form of a date.

However, a cell might contain text that looks like a date. Sometimes
that happens when you import data or copy-and-paste from some
applications into Excel.

Also, if you enter dates in a form that Excel does not recognizes as
such -- for example, 9.4.2010 -- it will be stored as text. You might
call it a date, but it's not. This miscommunication will continue
until you finally show us the exact contents of the cells.

 

[SS]

I got it to work for me in a round about way.
I just subtracted one from the other and this gave an answer in days so
just divided by 365
The answer is accurate enough for my purposes.

Thanks for all who replied.
I will come back to this post to get the info posted when I get other parts
of my spreadsheet sorted and spend more time on the `time issue`

thanks to all


Errata for typos and copy-and-paste errors....

=DATEDIF(A1,A2,"y") & " years " & DATEDIF(A1,A1,"ym") & " months"

=DATEDIF(A1,A2,"y") & " years " & DATEDIF(A1,A2,"ym") & " months"

=IF(EDATE(A1,12*DATEDIF(A1,B1,"y")+12)=B1,
DATEDIF(A1,B1,"y")+1 & " years 0 months",
DATEDIF(A1,B1,"y") & " years " & DATEDIF(A1,B1,"ym") & " months")

=IF(EDATE(A1,12*DATEDIF(A1,A2,"y")+12)=A2,
DATEDIF(A1,A2,"y")+1 & " years 0 months",
DATEDIF(A1,A2,"y") & " years " & DATEDIF(A1,A2,"ym") & " months")