How do I get the date of birth of a user to calculate and display the user’s age?
I use a formula like this in the sheet where A2:A just checks if the row is populated and M2:M is the column that contains the date of birth:
=ARRAYFORMULA(IF(LEN(A2:A) = 0, "", "**Age:** " & IF(M2:M = "", "", DATEDIF(M2:M, TODAY(), "Y"))))
My mind is a bit blown right now. LOL. So A2 and M2 are cells on the spreadsheet?
Whats the A and M then?
Can you send a screen shot or something of what you did?
Then how do you display it?
So the whole formula goes into one cell?
You can start out simpler and use
=DATEDIF(M2, TODAY(), “Y”) but you would have to put the formula in each row. Eventually you will find that arrayformula’s work much better. I recommend reading up on how arrayformulas work.
I’m assuming that you would place the formula on row 2. That is the only place you would need put the formula. All of the following rows will automatically apply the formula. The A2:A tells the formula to start at column A row 2 and apply the formula down the entire A column.
I’m displaying the age in my app as part of a bunch of markdown. It might be confusing to show you all of that. You can you a basic text or rich text to display the value.
Cool thanks. I will read up on it first.
Learned ARRAYFORMULA. It worked.
You’re a genius
You need to divide by 365.25 to cover leap years.
I did a quick test and I believe that the date-time algorithm used by glide is leap year aware. I don’t believe you need to add the 1/4 year (365.25). I calculated age comparing a DoB of 2/28/2000 and 3/1/2000 vs the next leap year of 2/29/2024 and did the calculation with / 365 and / 365.25 and the result was the same to 4 places (24.0000 yo)
Ah, that is rather interesting. Thanks for looking into that. I’ll have a play later.
um… did you by chance use the same formula that @darren posted? (ie. with the
FLOOR function included?)
Floor rounds down to the nearest integer value, so the result is as expected. Here is what it looks like without using floor:
You are right. Good catch.