Calculate age based on birthdate

Platform discussions Special workflows & use cases
1

Hi there,

I use this formula to calculate age based on “birthday” date column (showing years) :

IF(MONTH(TODAY())>MONTH({Birthday});YEAR(TODAY())-YEAR({Birthday});IF(MONTH(TODAY())=MONTH({Birthday});IF(DAY(TODAY())>=DAY({Birthday});YEAR(TODAY())-YEAR({Birthday});YEAR(TODAY())-YEAR({Birthday})-1);YEAR(TODAY())-YEAR({Birthday})-1))

You could even chose to show “Happy Birthday !” on the exact day with this second formula, even though I find it more convenient to use the first formula and set up an automation to get notified on someone’s birthday, since it allows your “Age” column to be populated only by numbers, for instance if you want to calculate the average age in a team :

IF(MONTH(TODAY())>MONTH({Birthday});YEAR(TODAY())-YEAR({Birthday});IF(MONTH(TODAY())=MONTH({Birthday});IF(DAY(TODAY())>DAY({Birthday});YEAR(TODAY())-YEAR({Birthday});IF(DAY(TODAY())=DAY({Birthday});“Happy Birthday !”;YEAR(TODAY())-YEAR({Birthday})-1);YEAR(TODAY())-YEAR({Birthday});YEAR(TODAY())-YEAR({Birthday})-1);YEAR(TODAY())-YEAR({Birthday})-1))

I hope it can help anyone !

image

7 Likes
2

Thank you so much for this. Super helpful and just what i needed :slight_smile:

1 Like
3

Glad it could help you :slight_smile:

4

Thank you this was exactly what I was looking for!

1 Like
5

Hi and how to automate using a function field? I think this is not possible to get a notification using a function ?

6

Hi,

To get notifications, you should use the Automation feature, and set up something like “when this column reaches this value, then notify this person”
But I don’t know if this first column can be a formula one…

1 Like
7

This is great, thank you!

1 Like
8

Thanks, that’ll help a lot!

1 Like
9

Hi, thank you so much for this! Do you have a variation of this formula, that would show Years and months? Displaying for example “1 and 3 months”?

I am working on a project with Children where the months matter.

Thank you for your help!

1 Like
10

Hey, glad it could help !

I guess I would go with something like this :

CONCATENATE(YEAR(TODAY())-YEAR({birthday});" years and “;MONTH(TODAY())-MONTH({birthday});” months")

11

Date formulas can be complex. Quentin did a great job on this one!

It can be simplified somewhat by using the mostly undocumented YEARFRAC() function.

Here are some examples:

Years with decimal fractions: YEARFRAC(FORMAT_DATE({Date}),FORMAT_DATE(TODAY()))

Whole years: INT(YEARFRAC(FORMAT_DATE({Date}),FORMAT_DATE(TODAY()))

Whole years, whole months:INT(YEARFRAC(FORMAT_DATE({Date}),FORMAT_DATE(TODAY()))) & " Years " & INT(MOD(YEARFRAC(FORMAT_DATE({Date}),FORMAT_DATE(TODAY())), 1) * 12) & " Months"

Edit: Now fixed.

Jim - The Monday Man
**:magic_wand: Column Magic :sparkles: **- the magical columns toolbox
What is Make & How can it help you with monday?

1 Like
12

021
021748×351 22.8 KB

Thank you for your help sir! I tried, but it gave me an error, do you know how I could fix it?

13

020

Hi Sir,

Thank you so much for your help! I tried your formula, it returns the correct amount of years, but “0” months. Would you be able to let me know to to fix it?

14

@art

Oops. :grin:

Try this: INT(YEARFRAC(FORMAT_DATE({Date}),FORMAT_DATE(TODAY()))) & " Years " & INT(MOD(YEARFRAC(FORMAT_DATE({Date}),FORMAT_DATE(TODAY())), 1) * 12) & " Months"

Jim - The Monday Man

:magic_wand: Column Magic :sparkles:- the magical columns toolbox app

15

Hi J Correll, thank you sooo much! It worked! I really appreciate all your time and help in with this issue!! have a great day!

2 Likes
16

Excellent! Thank you :slight_smile: :grinning:

3 Likes
17

The formula is great! Can you please create a version that will return a blank if there is no birthday given? I’ve tried but can’t get it to work, it just gives me an invalid parameter error. Thanks!

19

Thank you, this worked perfectly.