=DATEDIF(G3,G17,"y") & "years," & DATEDIF(G3,G17,"ym") & "months,"&
DATEDIF(G3,G17,"md") & "days,"
For more help on the undocumented DATEDIF function see Chip Pearson's site.
http://www.cpearson.com/excel/datedif.aspx
Gord Dibben MS Excel MVP
On Wed, 12 May 2010 13:43:01 -0700, C wrote:
Hi I'm hoping someone can point me in the right direction. I tried the
formula listed here and a few others and I can seem to get the right result.
I need a column to calculate age. I have todays date in G17 and the DOB in G3.
"T. Valko" wrote:
Try this:
E16 = start date
F16 = end date
=SUMPRODUCT(--(DAY(ROW(INDIRECT(E16&":"&F16)))=20))
That will count how many 20th of the months there are from a start date to
an end date (inclusive).
--
Biff
Microsoft Excel MVP
"VSlaybaugh" wrote in message
...
I have tried this function but am having trouble with it returning the
right
value. I have an end-date of 1/20/2010 (in cell F16) and a start-date of
2/11/2009 (in cell E16). I'm trying to calculate how many automatic
monthly
transfers there will be on the 20th of each month but the function is
returning a value of 11 when it should return a value of 12. The first
transfer will be 2/20/2009 and the last transfer will be 1/20/2010.a total
of
12 transfers.
"David Biddulph" wrote:
I hope that Dec 2008 - Jan 2006 is 35, rather than 23. :-)
An alternative to DATEDIF would be
=MONTH(A1)-MONTH(B1)+12*(YEAR(A1)-YEAR(B1))
Note that if you have different dates within the start and end months,
you'll get different results from the 2 formulae.
End of Jan to beginning of Feb gives a 1 month difference with the
original
=MONTH(A1)-MONTH(B1) and with my extension thereto, but DATEDIF counts
completed months (with various questions when months are of unequal
length).
--
David Biddulph
"Ron Rosenfeld" wrote in message
...
On Sun, 30 Dec 2007 18:37:01 -0800, Mike
wrote:
=MONTH(A1)-MONTH(B1)
That works when the two months are in the same year, but what if they
are
in
different years? For example January 2008-September 2007 = -8, but
should
equal 4. Or December 2008-January 2006 = 11, but should equal 23.
Use the undocumented DATEDIF function:
=DATEDIF(B1,A1,"m")
(The first date must be the earlier date).
See http://www.cpearson.com/Excel/datedif.aspx for documentation.
--ron