Monday, May 31, 2010

Excel Formula : Calculating Age

Today I am sharing with a long formula for calculating age based in Date of Birth as input.

=YEAR(DATE(YEAR(TODAY())-YEAR(A1),MONTH(TODAY())-MONTH(A1)+1,DAY(TODAY())-DAY(A1)+1))-1900&" Years "&IF(MONTH(DATE(YEAR(TODAY())-YEAR(A1),MONTH(TODAY())-MONTH(A1)+1,DAY(TODAY())-DAY(A1)+1))-1=0,"",MONTH(DATE(YEAR(TODAY())-YEAR(A1),MONTH(TODAY())-MONTH(A1)+1,DAY(TODAY())-DAY(A1)+1))-1&" Months ")&IF(DAY(DATE(YEAR(TODAY())-YEAR(A1),MONTH(TODAY())-MONTH(A1)+1,DAY(TODAY())-DAY(A1)+1))-1=0,"",DAY(DATE(YEAR(TODAY())-YEAR(A1),MONTH(TODAY())-MONTH(A1)+1,DAY(TODAY())-DAY(A1)+1))-1&" Days")

This formula calculates age of a person with the accuracy of exact years , months and days

This considers that the Date of Birth is available at Cell A1 of the worksheet. You will need to replace this

Download excel file containing formula to calculate age based on Date of Birth

Posted by Yogesh Gupta at 9:16 PM
Labels: , ,

26 comments:

  1. UnknownMay 31, 2010 at 11:26 PM

    A shorter formula:

    =CONCATENATE(DATEDIF(A1,TODAY(),"y")," years ",MOD(DATEDIF(A1,TODAY(),"m"),12), " Months")

    ReplyDelete
  2. UnknownJune 1, 2010 at 4:36 PM

    jialin: pretty good, but don't think DATEDIF is in Excel 2007. It was in earlier versions, right? Perhaps something similar can be accomplished with DAYS360?

    I was thinking something along these lines:
    =TEXT(TODAY()-A1,"YYYY")-1900&" Years "&MONTH(TODAY()-A1)-1&" Months "&DAY(TODAY()-A1)&" Days"

    Still, all of our formulas needed to be modified to include logic for singular values and zero values for all three: years, months, and days.

    ReplyDelete
  3. Familie Van HeystJune 1, 2010 at 5:14 PM

    Hello Mr Gupta

    Following is not very short but comes close to figure out the remaining nights sleep till the next birthday. Here C1=date today b2 = date of birth and d2 = calculated birthday this year
    =IF(MONTH($C$1)<MONTH(B2);"still "&(DATEVALUE(D2))-TODAY()&" nights sleep";IF(AND(MONTH($C$1)=MONTH(B2);DAY($C$1)<DAY(B2));"still "&(DATEVALUE(D2))-TODAY()&" night(s) sleep";IF(AND(MONTH($C$1)=MONTH(B2);DAY($C$1)=DAY(B2));"**Heep Heep Huray !!!!**";"still "&EDATE(Datevalue(D2);12)-TODAY()&" nights sleep")))

    ReplyDelete
  4. UnknownJune 1, 2010 at 6:50 PM

    @Squash DATEDIF works in 2003 and 2007 as well. It is just that it is not a regular excel function. It is a VBA function and hence there is no help available in excel and the argument directions don't turn up.

    ReplyDelete
  5. AmitJune 26, 2010 at 7:16 PM

    Hello Yogesh ji,

    I need a help from you.

    I want to put a formula in sheet2 which should calculate as per the last (filled) row of sheet1.

    for eg.
    in sheet1 Cell A61 is 408 & B61 is 203... I want to show the sum of these two in the Cell A12 of sheet2.. but I want the firmula to use the lastest row of Sheet1... say if A62 is 410 & B62 is 200 then A12 of sheet2 should show "610" and like wise everytime there is new entry in sheet1.

    hope I am clear in my question.

    Thanks

    ReplyDelete
  6. UnknownJune 26, 2010 at 8:49 PM

    I'm not sure why you posted this particular question under this blog article. However, assuming there are **no** blank cells in Column A (which I'm assuming will always have the last value on the sheet in it), then try this formula...

    =LOOKUP(2,1/(A1:A65535<>""),A:A)+INDEX(B2:B10,COUNTA(A:A))

    Rick Rothstein (MVP - Excel)

    ReplyDelete
  7. Yogesh GuptaJune 26, 2010 at 10:22 PM

    Hi Amit

    You can use following formula to get the last entered numeric value in column A of sheet1

    =INDEX(Sheet1!A:A,MATCH(9.99999999999999E+307,Sheet1!A:A))

    I hope you will be able to modify this to get value in column B

    Regards//Yogesh Gupta

    ReplyDelete
  8. AmitJune 29, 2010 at 1:27 PM

    Hello Yogesh ji,

    thanks for the formula, its working.

    @Rick,

    thanks

    ReplyDelete
  9. UnknownJuly 10, 2010 at 2:59 PM

    hi yogesji, i have an excel sheet having multiple data and every data row have a blank row beetween two rows. i want to delete the blank row in one shot, is it possible pls. help me.

    ReplyDelete
  10. UnknownNovember 24, 2010 at 9:41 AM

    Hai Sir, you done great job for intermediate learner for excel we want to some accounting templates like daybook, ledger and balance sheet:
    once we enter the transaction that can automatically updated in ledger and balance sheet please send me my mail id is razaravi@gmail.com

    ReplyDelete
  11. UnknownApril 23, 2011 at 10:28 AM

    This comment has been removed by the author.

    ReplyDelete
  12. UnknownApril 23, 2011 at 10:30 AM

    Dear SIr,
    can we calculate age by the following formula. Please answer me. (put DOB at Cell A1)

    =+DATEDIF(A1,NOW(),"Y")&" Years, "&DATEDIF(A1,NOW(),"ym")&" Months, "&DATEDIF(A1,NOW(),"md")&" Day."


    From: Sujit Dhar
    sujit_dhar1025@hotmail.com

    ReplyDelete
  13. Manoj1807November 30, 2011 at 11:53 AM

    Try This ;)

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

    ReplyDelete
  14. UnknownDecember 8, 2011 at 2:36 PM

    Sir,

    My self santosh I want to some information about my excel sheet simplifying.

    ReplyDelete
  15. anoop singhDecember 16, 2011 at 1:02 PM

    sir, my self anoop singh. I want to pint a check book in excel work sheet, but i hve sme prblm like i want to print numeric value in text. Supose 120460.But spellcurr()formula RETURN value one hudred twnty thousnd four hundrd sixty only. But I want print "One lack twenty thounsd four hundrd sixty only"
    I hope Sir u help me better.

    ReplyDelete
  16. GfizzDecember 22, 2011 at 10:43 PM

    I'm using the =INT((TODAY()-AB2)/365.25). It does give me the age, but if I don't have a date in the birthday cell(AB2) I get a "111" in the age cell.
    How can I make the age cell blank if there is no date in the birthday cell? Thanks!

    ReplyDelete
  17. ShishirJanuary 13, 2012 at 6:27 PM

    hi there
    i recently created a worksheet for paid and unpaid invoices and just need to know if i can somehow put a tick and a cross sign showing whether it has been paid or not.
    If so please help me out.
    Regards,
    Shishir Sharma

    ReplyDelete
  18. Arvind RathiMay 20, 2012 at 9:20 PM

    @gfizz....if(A! <> "",int((today()-A1)365.25),"no date")

    this is just if operator logic...if u find other logic plz let me know

    ReplyDelete
  19. UnknownNovember 29, 2012 at 5:06 PM

    =DATEDIF(Bd,TODAY(),"y") & " Years "&DATEDIF(Bd,TODAY(),"ym") & " Months "&DATEDIF(Bd,TODAY(),"md") &" Days"

    ReplyDelete
  20. UnknownDecember 14, 2012 at 1:49 AM

    hello, i need to know how to convert word to number and number to word in excel 2007. i've seen your spellcurr formula but i don't have it.

    ReplyDelete
  21. UnknownDecember 14, 2012 at 11:10 PM

    i wanto view to oracle data .dat file in excle sheet.

    ReplyDelete
  22. मनीष शंकर तिवारीApril 20, 2013 at 10:25 AM

    Yogesh ji please help me: How can i convert Date into Text format i.e. 17-08-1977 as Seventeenth August Nineteen Seventy Seven.. plz

    ReplyDelete
  23. UnknownJuly 19, 2013 at 2:35 PM

    Dear sir,

    Please help me,

    I update in excel of our Sales order, when its gone out I put Date of dispatch,

    I want when I put date, it should be, Hide in the next sheet, only show pending order which is not gone.

    if its possible please inform me sir,

    ReplyDelete
  24. UnknownJune 24, 2015 at 4:07 PM

    Dear Sir,

    Can we have the formula for tracking the change in number of two cloumns by auto change in the cell color ( +ve, -ve and equal values)

    ReplyDelete
  25. UnknownJuly 5, 2016 at 12:03 PM

    nice information i found online date of birth calculator http://www.onlinecalculator1.com/Date-of-birth-calculator.php

    ReplyDelete
  26. UnknownJuly 5, 2016 at 12:03 PM

    nice information i found online date of birth calculator http://www.onlinecalculator1.com/Date-of-birth-calculator.php

    ReplyDelete

Subscribe to: Post Comments (Atom)