If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Formula to calculate an age from a date of birth
I'm looking for the formula to calculate the age from a date of birth.
|
#2
|
|||
|
|||
Formula to calculate an age from a date of birth
One way:
=DATEDIF(A1,TODAY(),"y") where A1 contains the DOB. See http://cpearson.com/excel/datedif.htm for documentation on DATEDIF(). In article , Lisa C wrote: I'm looking for the formula to calculate the age from a date of birth. |
#3
|
|||
|
|||
Formula to calculate an age from a date of birth
for more than just years, just expand.
from xlfdic01.xls, if date is in D34... ="Age is "&DATEDIF(D34,TODAY(),"y")&" Years, "&DATEDIF(D34,TODAY(),"ym")&" Months and "&DATEDIF(D34,TODAY(),"md")&" Days" --- Message posted from http://www.ExcelForum.com/ |
#4
|
|||
|
|||
Formula to calculate an age from a date of birth
However, be very careful about days - DATEDIF() assumes that a month is
as long as the number of days in the first date's month, so if D34: 31 January 1980 on 1 March 2005, the result will be: Age is 25 Years, 1 Months and -2 Days Some people may not feel -2 days is valid. In article , firefytr wrote: ="Age is "&DATEDIF(D34,TODAY(),"y")&" Years, "&DATEDIF(D34,TODAY(),"ym")&" Months and "&DATEDIF(D34,TODAY(),"md")&" Days" |
#5
|
|||
|
|||
Formula to calculate an age from a date of birth
I use =(TODAY()-A1)/365.25 where DOB is in A1............
Vaya con Dios, Chuck, CABGx3 "Lisa C" wrote in message ... I'm looking for the formula to calculate the age from a date of birth. |
#6
|
|||
|
|||
Formula to calculate an age from a date of birth
Hi Lisa!
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). Age in Weeks and Days: =IF(A1B1,IF(INT(DATEDIF(A1,B1,"d")/7)=0,MOD(DATEDIF(A1,B1,"d"),7),INT(DATEDIF(A1,B1," d")/7) & " wk " & MOD(DATEDIF(A1,B1,"d"),7))&" d","-"& IF(INT(DATEDIF(B1,A1,"d")/7)=0,MOD(DATEDIF(B1,A1,"d"),7),INT(DATEDIF(B1,A1," d")/7) & " wk " & MOD(DATEDIF(B1,A1,"d"),7))&" d") This one doesn’t report “wk” if the number of days is =6. Also it allows the start date to be later than the end-date and in those cases precedes the entry with a “-“ -- Regards Norman Harker MVP (Excel) Sydney, Australia "Lisa C" wrote in message ... I'm looking for the formula to calculate the age from a date of birth. |
Thread Tools | |
Display Modes | |
|
|