Hello,
I have 2 columns Date_of_Birth and Age_Group. I have to calculate age group in a range from given date of birth as mentioned below:
If the age falls between 0 month to 1 Year Age Group should be “0-1 Yrs” and so on. “1-2 Yrs”, “2-5Yrs”, “5-6 Yrs”, “6-10 Yrs”, “10-19 Yrs”, “19-49 Yrs”, “49-60 Yrs” and “60+ Yrs”, further data entry is based on Show_If(age_Group=“19-49 yrs”) and so on.
Kindly guide me achieve this.
Thank & Regards
You would need to utilize a large IFS expression.
Please try an expression something like below
IFS(
[Date_of_Birth]>TODAY(), “Invalid date”,
EOMONTH([Date_of_Birth],11)+DAY([Date_of_Birth])>=TODAY(), “0-1 Yrs” ,
EOMONTH([Date_of_Birth],23)+DAY([Date_of_Birth])>=TODAY(), “1-2 Yrs”,
EOMONTH([Date_of_Birth],59)+DAY([Date_of_Birth])>=TODAY(), “2-5 Yrs”,
EOMONTH([Date_of_Birth],71)+DAY([Date_of_Birth])>=TODAY(), “5-6 Yrs”,
EOMONTH([Date_of_Birth],119)+DAY([Date_of_Birth])>=TODAY(), “6-10 Yrs”,
EOMONTH([Date_of_Birth],227)+DAY([Date_of_Birth])>=TODAY(),“10-19 Yrs”,
EOMONTH([Date_of_Birth],587)+DAY([Date_of_Birth])>=TODAY(),“19-49 Yrs”,
EOMONTH([Date_of_Birth],719)+DAY([Date_of_Birth])>=TODAY(),“49-60 Yrs”,
TRUE, “60+ Yrs”
)
Please test well, especially for edge cases. You can use this column further in your dependent calculations.
Sorry Marc. I had not seen you had already replied. Yes, as you mentioned, it is a large IFS() expression.
I’d do the following:
Create a two-column table, name it ageGroups.
In your original table:
Add a new virtual column that contains the age in days, name it say ageDays. It’s app formula will be:
TODAY() - [birthDate]
You might rather want to calculate the age at a specific moment, for example at the time of subscription to a service or enrolment in a class, etc. rather than on a daily basis. In that case, you can use a normal column with the app formula:
[subcriptionDate] - [birthDate]
Your Age_Group column now should be of type Ref pointing to the ageGroups table, with the following app formula:
MIN( FILTER(“ageGroups”, [ageGroupKey] >= [_ThisRow].[ageDays]) )
And I haven’t seen yours 
Yes, I agree, a lookup table is the way to go - for two reasons
You are using this in multiple places
With a table, should you decide to adjust your age-groups (happens all the time), then you only need to change the table - not the many places in the app.
CAUTION: Considering 2) above, I would NOT use the “age days” as the Key. Instead use a dedicated key column and set the age days as just a data column. You’ll need to adjust the expression slightly but functionally it will still be the same. This helps should you ever need to adjust the age groups and need to re-classify rows - far fewer rows to change.
I agree. It is just a very small dataset so I wanted to make the smallest expression in this case, but generally you are totally right!
No worries 
. More solutions/ options are always good to have.
@WillowMobileSys : Maybe I am missing some details. Could you update why you felt this is being used in multiple places. I did not find that being mentioned in original post. By multiple places do you perhaps mean in each row of the table?
This comment implies that Age Grouping implementation is needed on the Show_If as well as the usual classification on the row. While I understand that you can set a single value on the row and then reference it, it indicates the logic is needed in multiple places. Maybe that’s all but based on my experiences with this type of app, one that classifies on age groups, it’s likely this same logic will be needed in many different places as the app expands - especially when implementing the reporting that normally goes along with this type of app.
Regardless, when you fold in the likely need to adjust age group durations, the ability to adjust a table rather than needing to re-deploy the app is a big benefit.
I’d like to add that employing lookup tables is a widely used practice in programming in general not just in databases. In low-level programming a lot of DateTime and Math functions for example are deployed as lookup tables to save processing power.
Thank you very @WillowMobileSys and @Joseph_Seddik for valuable insights. I will definitely keep it at the back of my mind your thought process.
I understood you mentioned likely need of adjustment in age group and use at multiple places as salient points in favor of lookup table.
@Suvrutt_Gurjar Welcome my friend ! The thanks are due to yourself for the numerous things I learned from your posts over the years..
For the matter at hand, I’d say that a Read is always better than a Calculate. For this an important step that is unfortunately overlooked by too many developers is to spend time thinking about the most efficient data modelling before taking any step in app creation. I’ve seen many apps for example having serious performance issues that cannot be improved in any way from within the app due to flawed data modelling and inefficient table schemas.
Also as far as AppSheet or a database in general is concerned, dividing complex calculations among several additional columns to read a value afterwards through a simple dereference expression, is better than having a large complex expression in just one column.
Thank you Josef for good words for me and more practical insights. Those are useful.
I am aware you have given this as a useful general guideline. However, in this particular case of the current post, I may humbly say that it is a rather long IFS() expression with multiple similar conditions than a complex expression. It is using values from just the current row.
Coming back to our current requirement
@Joseph_Seddik wrote:
Your Age_Group column now should be of type Ref pointing to the ageGroups table, with the following app formula:
MIN( FILTER(“ageGroups”, [ageGroupKey] >= [_ThisRow].[ageDays]) )
May I know what kind of column you have proposed for the [Age_Group] - physical or virtual? Is it virtual?
I frequently use long IFS() expressions in virtual columns running on thousands of rows with absolutely no issue. What matters is the expressions inside. I agree yours is not complex. It was rather a general guidance, not related at all to this expression. Thank you for the added clarification!
Virtual with no doubt! I propose expressions always for the case at hand, and here a very small dataset, few dozen lines, or even a couple of hundreds or so, it won’t be an issue.
To calculate Age_Group from Date_of_Birth, you first need to calculate the current age in years (or months for infants), then use conditional logic to assign the correct range. Here’s a sample logic you can apply in Excel, Google Sheets, or a script (like Apps Script or SQL):
Step 1: Calculate Age
In Excel/Sheets:
=DATEDIF(Date_of_Birth, TODAY(), “Y”)
For infants less than 1 year, calculate in months:
=DATEDIF(Date_of_Birth, TODAY(), “M”)
Step 2: Assign Age_Group using nested IFs
=IF(DATEDIF(A2,TODAY(),“M”)<12, “0-1 Yrs”,
IF(DATEDIF(A2,TODAY(),“Y”)<2, “1-2 Yrs”,
IF(DATEDIF(A2,TODAY(),“Y”)<5, “2-5 Yrs”,
IF(DATEDIF(A2,TODAY(),“Y”)<6, “5-6 Yrs”,
IF(DATEDIF(A2,TODAY(),“Y”)<10, “6-10 Yrs”,
IF(DATEDIF(A2,TODAY(),“Y”)<19, “10-19 Yrs”,
IF(DATEDIF(A2,TODAY(),“Y”)<49, “19-49 Yrs”,
IF(DATEDIF(A2,TODAY(),“Y”)<60, “49-60 Yrs”,
“60+ Yrs”))))))))
Step 3: Use Show_If Logic
Based on the value in Age_Group, apply your form’s conditional logic like:
Show_If(age_Group = “19-49 Yrs”)
Let me know if you’re using a specific platform (Excel, Google Sheets, AppSheet, SQL, etc.) and I can tailor the solution accordingly.