# Re: Calculating Age on a Given Date

Solved
2789 0
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.)

17 Replies 17
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?

7 - App Architect

As Justin said the answer was in the thread and provided something I could not do myself.
Best of luck with it.

Phil

5 - Automation Enthusiast

Yes @Philip_Barber and @Justin_Barrett I tried pasting the formula in but it marked error. I do not need the date AS OF a specific date, or do I need to add a specific date?

for example what does DATE_TIME PARSE mean?

I used this example: DATETIME_DIFF(TODAY(11-18-2020),{DOB LInk},‘M’)/12

5 - Automation Enthusiast

Yes I have. They are marking Error.

7 - App Architect

Hi Antonia,

I am not an expert and I relied on somebody else in the community answering my question and the formula that they gave me is …. DATETIME_DIFF(DATETIME_PARSE(‘11-11-2019’,‘DD-MM-YYYY’),{DOB LInk},'Y’)

In my case the child gave their specific DOB and this then calculated what their age would be on a specific date in the future.

I hopes this helps

Phil

7 - App Architect

I have just been notified that “My reply to your problem has not been sent because they couldn’t find content” I my email!!!

If you want to see my formula please reply directly to me at phil.barber@sky.com and I will send it to you.
Regards
Phil

5 - Automation Enthusiast

Thank you!

Antonia Lavender
310.803.6854
Antonia@AntoniaLavender.com

I see two problems:

1. The ‘M’ string is using styled quotes. Airtable formulas only work with non-styled quotes. Notice the very subtle difference between ‘M’ and `'M'`.
2. You added numbers inside the call to the `TODAY()` function. `TODAY()` doesn’t accept any arguments. Even if it did you can’t just type a date and have it interpreted as a date.

The correct formula should be:

``````DATETIME_DIFF(TODAY(), {DOB Link}, 'M') / 12
``````
5 - Automation Enthusiast

Justin, you are a GODSEND! I figured it out and it works! Thank you!!