The Airtable Community will undergo scheduled maintenance on September 17 from 10:00 PM PST to 11:15 PM PST. During this period, you may experience temporary disruptions. We apologize for any inconvenience and appreciate your understanding.

# Calculating Age on a Given Date

Topic Labels: Formulas
Solved
8355 18
cancel
Showing results for
Did you mean:
7 - App Architect

I am a real novice at formulas and I have been unable to work out what I have done wrong. Can somebody please tell me.

I am trying to return the age of a person based on the difference between the DOB and the date in the future. What I have here works sometimes but sometimes gives the incorrect age. The problem occurs if the dob is later in the year than todays date.

### AYM Festival test 2019 - Airtable

Explore the "AYM Festival test 2019 " base on Airtable.

Hopefully the link will show that the first age (for Jacob) is correct but the second line for (Peter) is one year out.

1 Solution

Accepted Solutions

Grrr… I copy-and-pasted your formula with the changes — but I missed the curly quotes around the ‘Y’. The forum software here ‘prettifies’ them… but Airtable needs `'Y'` instead of ‘`Y`’.

Overtype them with the straight quote (often the unshifted `"` to the left of the `Enter` key, but possibly the unshifted `@` two keys to the left — or possibly something entirely different :winking_face: ). Or copy and paste this version:

`DATETIME_DIFF(DATETIME_PARSE('01-11-2019','DD-MM-YYYY'),{DOB Link},'Y')`

(Again, that uses 1 November 2019 as the match date; if you want 11 November, you’ll need to correct.)

18 Replies 18
13 - Mars

Well, Peter is 6 years old — 6 years and 11 months, that is. `DATETIME_DIFF()` returns the number of whole years difference between the two dates; months are truncated.

If you want to show Peter as being 7 years old when the ‘today’ date is nearly his birthday, you can try this:

`DATETIME_DIFF(TODAY(01-11-2019),{DOB LInk},‘M’)/12`

Formatted with a single decimal place, that will show Peter’s age as `6.9`; formatted as an integer, it shows `7`. You can also use `ROUND()`, `ROUNDUP()`, or `ROUNDDOWN()` if you need more control.

Note: The ‘M’ used as a unit specifier must be capiltalized. Airtable accepts both ‘y’ and ‘Y’ for a years specifier, but lower-case ‘m’ means ‘minutes’, while upper-case means ‘Months’.

7 - App Architect

Thank you so much for your response but I must have explained my problem incorrectly.
On the 11th November Peter will be 7 years 4 months and 10 days old.
I want the calculation in Airtable today to predict he will be 7 in November.

Ah, got it. (FYI, you have November 1, not November 11, as your ‘today’ date.)

Instead of `TODAY()`, you want `DATETIME_PARSE()`, and you may want to provide a format specifier to make sure it doesn’t make the assumption I did. (It should use your browser preferences to set day/month precedence, but with a format specifier, there’s no question.)

``````DATETIME_DIFF(DATETIME_PARSE('01-11-2019','DD-MM-YYYY'),{DOB LInk},‘Y’)
``````

I was focusing on the integer issue and overlooked you were trying to force `TODAY()` to a date other than today…

7 - App Architect

Thank you so much for trying to answer my problem.

Not wanting to miss type any small detail of your solution I copied it and pasted it into the formula but it came back as below.

I of course have no idea which part of it is wrong.

I really do appreciate your time and effort on my problem and I guess it is something I have done incorrectly but I am at a loss.

Grrr… I copy-and-pasted your formula with the changes — but I missed the curly quotes around the ‘Y’. The forum software here ‘prettifies’ them… but Airtable needs `'Y'` instead of ‘`Y`’.

Overtype them with the straight quote (often the unshifted `"` to the left of the `Enter` key, but possibly the unshifted `@` two keys to the left — or possibly something entirely different :winking_face: ). Or copy and paste this version:

`DATETIME_DIFF(DATETIME_PARSE('01-11-2019','DD-MM-YYYY'),{DOB Link},'Y')`

(Again, that uses 1 November 2019 as the match date; if you want 11 November, you’ll need to correct.)

7 - App Architect

It seems so formal to call you W_Van but I do want to thank you for solving the problem.

Thank you for spending your time to go through in such detail a problem that I had no idea as to how to solve.

Airtable is great however I am not sure I will ever understand formulas!!!

All the best
Phil
PS I did copy and paste and I have altered to the 11th Nov

I’m just sorry I had to back up and run over the problem several times before I got it right. I blame it on the drugs — I just underwent my fourth oral surgery in two months, which means in a few days I should be pain- and painkiller-free for the first time since late March. The meds won’t let me sleep, but they don’t let me think, either… as I seem to discover every time I reply to a post. :winking_face:

5 - Automation Enthusiast

I was hoping you could help me. I have NO experience in programming and would like to link a birthdate to automatically change the AGE on another column. Could you please type out a funny looking formula for me to do that? Thank you!!!

Welcome to the community, @Antonia_Lavender! :grinning_face_with_big_eyes: Have you tried using the formulas listed earlier in this thread?