Help with formula

  • Thread starter Thread starter Dick
  • Start date Start date
D

Dick

I have a date in cell a1, example 03/04/2007. In cell b1 I would like
a formula to show the total years, months, and days. from the date in
cell a1 and would automatically update each day. Thanks in advance!!!
 
Dick said:
I have a date in cell a1, example 03/04/2007. In cell b1 I would like
a formula to show the total years, months, and days. from the date in
cell a1 and would automatically update each day. Thanks in advance!!!
Click to expand...


=year(a1)&" years, "&month(a1)&" months, "&day(a1)&" days"

but I doubt that is really what you are looking for.

=DATEDIF(A1,TODAY(),"y")&" years, "&
DATEDIF(A1, TODAY(),"m")&" months, "&
DATEDIF(A1,TODAY(),"d")& " days"

[ this is all a single line ... delete the line breaks and copy/paste
into b2 ]

probably is closer, but still not what you want. Look at Chip Pearson's
examples and you should be able to get what you are after.

DATEDIF can give you the number of complete calendar months between
two dates. The syntax is:

=DATEDIF(Date1, Date2, Interval)

where Interval would be "m".

Chip Pearson has more examples of this undocumented function here:

http://www.cpearson.com/excel/datedif.aspx

Hope this helps.
 
Clif McIrvin said:
=year(a1)&" years, "&month(a1)&" months, "&day(a1)&" days"

but I doubt that is really what you are looking for.
Click to expand...

I'm not sure how this compares to the function Ron posted; he did an
excellent job of explaining the difficulties in answering your question.

Here's another idea to consider - I spread it out over several cells so
you can see what each piece of the formula is doing. First, with your
sample date in A1, here are the results:

3/4/2007 Date
1474 Days from today
1/13/1904 expressed as an Excel date
4 years from today
0 months from today
13 days from today

and the formulas I used:

=DATEDIF(A1,TODAY(),"d") Days from today
=DATE(0,1,A2) expressed as an Excel date
=YEAR(A3)-1900 years from today
=MONTH(A3)-1 months from today
=DAY(A3) days from today

This might actually give you what you are looking for. I didn't check to
see what this gives you if you choose a month other than todays month.

Clif
=DATEDIF(A1,TODAY(),"y")&" years, "&
DATEDIF(A1, TODAY(),"m")&" months, "&
DATEDIF(A1,TODAY(),"d")& " days"

[ this is all a single line ... delete the line breaks and copy/paste
into b2 ]

probably is closer, but still not what you want. Look at Chip
Pearson's examples and you should be able to get what you are after.

DATEDIF can give you the number of complete calendar months between
two dates. The syntax is:

=DATEDIF(Date1, Date2, Interval)

where Interval would be "m".

Chip Pearson has more examples of this undocumented function here:

http://www.cpearson.com/excel/datedif.aspx

Hope this helps.
Click to expand...
 
Ron Rosenfeld said:
is equivalent to =TODAY()-A1


One of several problems with DATEDIF is that, when "counting months",
it assumes the "month ending" date is that of the start date. So this
can cause a problem in a variety of circumstances.

In addition, beginning with Excel 2007 SP2, the "md" argument gives
incorrect results in some instances.
Try: Start Date: 27 Jun 2009
End Date: 5 Jan 2012

=DATEDIF(Start_Date, End__Date,"md") --> 122

So this function is "broken" and not officially supported by MS.
Although it has worked in the past, with the EOM limitations
mentioned, it seems to be getting less reliable in more circumstances.
I hesitate to recommend it any more.
Click to expand...
 
Back
Top