Hi Kapi!
You'll find Chip Pearson a great source on dates and especially on the
details of the DATEDIF function.
But here's a selection. Take your pick. I've used A1 as the base but
all you need to do is replace A1 with TODAY()
It's an old problem and can be answered in different way. Here's a
summary of tried and tested formulas:
In all cases I use:
A1
23-Feb-1947
B1
2-Feb-2003
Rather than B1 you might substitute TODAY(). But note that TODAY() is
volatile and recalculates each time the worksheet recalculates. If you
want to 'fix' on today's date enter the date manually or use the
keyboard shortcut Ctrl + ;
Age in completed years:
=DATEDIF(A1,B1,"y")
returns 55
Age in completed months:
=DATEDIF(A1,B1,"m")
returns 671
Age in completed days:
=DATEDIF(A1,B1,"d")
returns 20433
OR
=B1-A1
returns 20433
Age in years and completed months:
=DATEDIF(A1,B1,"y") & " y " & DATEDIF(A1,B1,"ym") & " m"
returns 55 y 11 m
Age in years and days:
=DATEDIF(A1,B1,"y") & " y " &
B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)) & " d"
returns 55 y 344 d
(Note: DATEDIF approach using "yd" produces errors)
Age in years, weeks, and days:
=DATEDIF(A1,B1,"y") & " y " &
INT((B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)))/7) & " w
" & MOD((B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1))),7) &
" d"
returns: 55 y 49 w 1 d
(Note: DATEDIF approach using "yd" produces errors)
Age in years and fractions of a year:
=DATEDIF(A1,B1,"y")+(B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY
(A1)))
/(DATE(YEAR(A1)+DATEDIF(A1,B1,"y")+1,MONTH(A1),DAY(A1))-DATE(YEAR(A1)+
DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)))
returns: 55.94246575
(Note: YEARFRAC produces errors where dates are 1 or more years
apart).
Age in years, months and days:
=DATEDIF(A1,B1,"y") & " y " & DATEDIF(A1,B1,"ym") & " m " &
DATEDIF(A1,B1,"md") & " d"
returns: 55 y 11 m 10 d
(But note that this will produce some strange responses and sequence
interpretation difficulties due to the lack of a consistent definition
of a month).
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Saturday: Bosnia-Herzegovina (Petrovdan); Canada,
Northern Ireland, and Eire (Orangemen's Day); Kiribati (Independence
Day); Malaysia (Birthday of the Yang di-Pertua Negi Pulau Pinang);
Miconesia (Micronesian Day); Sao Tome & Principe (Independence Day).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
