May 31, 2019 09:41 PM
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.
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.
Solved! Go to Solution.
Jun 04, 2019 05:24 PM
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.)
Jun 01, 2019 03:36 PM
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’.
Jun 01, 2019 10:52 PM
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.
Jun 03, 2019 05:02 PM
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…
Jun 03, 2019 09:29 PM
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.
Jun 04, 2019 05:24 PM
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.)
Jun 04, 2019 10:42 PM
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
Jun 04, 2019 10:56 PM
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:
Nov 17, 2020 09:05 AM
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!!!
Nov 17, 2020 09:54 AM
Welcome to the community, @Antonia_Lavender! :grinning_face_with_big_eyes: Have you tried using the formulas listed earlier in this thread?
Nov 17, 2020 11:20 AM
As Justin said the answer was in the thread and provided something I could not do myself.
Best of luck with it.
Phil
Nov 18, 2020 09:30 AM
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
Nov 18, 2020 09:34 AM
Yes I have. They are marking Error.
Nov 18, 2020 10:40 AM
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
Nov 18, 2020 11:36 AM
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
Nov 18, 2020 02:47 PM
Thank you!
Antonia Lavender
310.803.6854
Antonia@AntoniaLavender.com
Nov 20, 2020 09:14 AM
I see two problems:
'M'
.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
Nov 23, 2020 02:02 PM
Justin, you are a GODSEND! I figured it out and it works! Thank you!!