Formulas: Age

  • Here's a brain twister for you to ponder...

    The following formulas both return good approximations for exact age.

    =(YEAR(today)-YEAR(DOB)) + ((MONTH(today)-MONTH(DOB))/12) + ((DAY(today)-DAY(DOB))/(365+DAY(DATE(YEAR(today),3,1)-1)-28))

    =DATEDIF(DOB,today,"y") + (DATEDIF(DOB,today,"ym")/12) + DATEDIF(DOB,today,"md")/(365+DAY(DATE(YEAR(C42),3,1)-1)-28)

    Which one is the most correct?

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Aaron,

    I think you will find that this gives the best results:

    =IF(DATEDIF(dob,today,"y") + (DATEDIF(dob,today,"ym")/12) + DATEDIF(dob,today,"md")/(365+DAY(DATE(YEAR(today),3,1)-1)-28)>39.99,"39.99",DATEDIF(dob,today,"y") + (DATEDIF(dob,today,"ym")/12) + DATEDIF(dob,today,"md")/(365+DAY(DATE(YEAR(today),3,1)-1)-28))


  • Weasel,

    I like your formula best mate, because if I use it I will never reach the age of 40!! :biggrin::biggrin:

    I can only ever be 39.99 :o

    Marvellous... unless it's some sort of Logan's Run thing ?? :wow:


  • Hehehe... that's pretty good. :biggrin:

    It will actually tell us what we think, amazing.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Actually, I think this formulation is a bit off, in that it (I think) treats all months as having the same number of days. i.e., it says that 1 July is 0.50000 of a year. Actually 1 July is only the 182nd day of the year (except Leap Years), and hence is less than 0.500000 if you are looking at the fraction of the total year.

    Guess it all depends on how you want to count.

  • Only recently registered with this forum and haven't quite got to grips with the layout so this may have been mentioned before. Anyway what I was going to say was have a look at this link:

    Apologies if it hsas already been covered


    shhhhh! no-one say "Chip" :yikes:

    <a href="" target="new"><img src=""></a>

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!