Calculating Age on a Given Date

[Philip_Barber]

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.

DATETIME_DIFF(TODAY(01-11-2019),{DOB LInk},‘Y’)

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.

Airtable

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.

[W_Vann_Hall]

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’.

[Philip_Barber]

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.

[W_Vann_Hall]

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…

[Philip_Barber]

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.

[W_Vann_Hall · Accepted solution]

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.)

[Philip_Barber]

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

[W_Vann_Hall]

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:

[Antonia_Lavende]

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!!!

[Justin_Barrett]

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