Help with formula

[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!!!

 

[Clif McIrvin]

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!!!

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

 

[Ron Rosenfeld]

Tagging on here as I don't see the original message.

I do read your question a little differently than Cliff and, if I were using DATEDIF, would use the formula below.

DATEDIF can work some of the time, but it has flaws.

For example, using the formula today:

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

with 3/4/2007 in A1 --> 4 years, 0 months, 12 days

which is probably correct.

But, if A1 = 1/31/2011 then that same formula --> 0 years, 1 months, 13 days
where you probably want a result of 1 Month, 16 days

The problem, of course, is that while "days" and "weeks" are fixed in length; months and years can vary in length.

The closest I've come to being able to solve the problem is with a User Defined Function.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

B1: =DateIntvl(A1,TODAY())

===================================
Option Explicit
Function DateIntvl(d1 As Date, d2 As Date) As String
'Note that if d1 = 29 Feb, the definition of a year
'may not be the same as the legal definition in a
'particular locale
'Some US states, for some purposes, declare a
'leapling's birthday on 1 Mar in common years; England
'and Taiwan declare it on Feb 28
Dim temp As Date
Dim i As Double
Dim yr As Long, mnth As Long, dy As Long
Dim sOutput() As String

Do Until temp > d2
i = i + 1
temp = DateAdd("m", i, d1)
Loop

i = i - 1
temp = DateAdd("m", i, d1)

yr = Int(i / 12)
mnth = i Mod 12
dy = d2 - temp

ReDim sOutput(0 To -(yr > 0) - (mnth > 0) - (dy > 0) - 1)
i = 0
If yr > 0 Then
sOutput(i) = yr & IIf(yr = 1, " Year", " Years")
i = i + 1
End If
If mnth > 0 Then
sOutput(i) = mnth & IIf(mnth = 1, " Month", " Months")
i = i + 1
End If
If dy > 0 Then sOutput(i) = dy & IIf(dy = 1, " Day", " Days")

DateIntvl = Join(sOutput, ", ")

End Function
=======================================

 

[Clif McIrvin]

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

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

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.

 

[Ron Rosenfeld]

=DATEDIF(A1,TODAY(),"d") Days from today

is equivalent to =TODAY()-A1

=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

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.

 

[Clif McIrvin]

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.