calculate age based on date of birth

  • 0
  • 2
  • Question
  • Updated 3 years ago
  • Answered
I have a date of birth field and I would like to have a formula field that calculates the student's age based on the date in that field.
Photo of Measuring

Measuring

  • 10 Points

Posted 6 years ago

  • 0
  • 2
Photo of dwhawe

dwhawe, Champion

  • 908 Points 500 badge 2x thumb
(ToNumber(ToText(Today()-[Date of Birth])))/8766

I am sure there is a better way but this is what I use to calculate current age as of today
Photo of Measuring

Measuring

  • 10 Points
Where does 8766 come from?
Photo of dwhawe

dwhawe, Champion

  • 908 Points 500 badge 2x thumb
Hours in a year based on a four year average to account for leap year.
Photo of David

David

  • 0 Points
find the month and year of a date
Photo of Hands

Hands

  • 0 Points
Is there a way to display the age as a whole number? I set the field to display 0 decimal places but once a person is halfway through the year it rounds up rather than down. Any suggestions?
Photo of Ninja

Ninja

  • 0 Points
I added the "Floor" to round it down to the nearest integer. I also added 24 hours to the amount of time being divided by 8766 so that it will increase the age on the person's birthday, rather than the day after. (it is very important to place the "24" between the correct parenthesis).

Floor((ToNumber(ToText(Today()-[Date of Birth]))+24)/8766 )
This is 100% accurate in perpetuity:



If(Today()>ToDate(ToText(Month([Birthdate]))&"-"&ToText(Day([Birthdate]))&"-"&ToText(Year(Today()))),
Year(Today())-Year([Birthdate]),(Year(Today())-(Year([Birthdate])))-1
)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
This does not account for leap seconds. Yes it is a real thing and the last leap second occurred on June 30, 2015 at 23:59:60!
It would appear that this formula is actually more accurate and much simpler. Thanks Matthew @ CirrusOps!

(Year(Today())-Year([Birthdate])-1)+
If(Month(Today())>Month([Birthdate]),1,0)+
If(Month(Today())=Month([Birthdate]) and Day(Today())>=Day([Birthdate]),1,0)