Return value if date falls within range

I have researched others' questions on this and I feel this should be right, but it's not.

I'm trying to return the generation based on DOB:

'=IF(DOB@row <= DATE(1943,12,31), "Traditionalists", IF(AND(DOB@row >= DATE(1944, 1, 1), DOB@row <= DATE(1959,12,31)), "Baby Boomers", IF(AND(DOB@row >= DATE(1960, 1, 1), DOB@row <= DATE(1980, 12, 31)), "Generation X", IF(AND(DOB@row >= DATE(1981, 1, 1), DOB@row <= DATE(2000, 12, 31)), "Millennials", IF(AND(DOB@row >= DATE (2001,1,1), DOB@row <= DATE(2010, 12, 31)), "Generation Z", "")))))

I have tried a few and this got the closest - it worked when I only had 2 ranges, but when I added more generations, it broke again.

Does anyone know what I've done wrong?

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Try this:

=IF(YEAR(DOB@row)<= 1943, "Traditionalists", IF(YEAR(DOB@row)<= 1959, "Baby Boomers", IF(YEAR(DOB@row)<= 1980, "Generation X", IF(YEAR(DOB@row)<= 2000, "Millennials", IF(YEAR(DOB@row)<= 2010, "Generation Z", "")))))

Paul Newcome

That is correct. The logic with nested IFs stops on the first true value. So if it is has flagged as true on the 5th argument then that means 1 through 4 must be false.

So to output "Baby Boomers", that means it flagged as false on the first one (year MUST be greater than 1943) but is also less than or equal to 1959. That logic follows through the rest of them.

Gen Z means that the year must be greater than 1943, greater than 1959, greater than 1980, and greater than 2000 (since it was false for all of them) and less than or equal to 2010 since that's the one that flagged as true.

All comments

Paul Newcome

Try this:

Nat

Thank you so much yet again! Clean and works perfectly!

Does the order you write it in mean if they're a millennial they can't be either of the ones that come before?

Paul Newcome

That is correct. The logic with nested IFs stops on the first true value. So if it is has flagged as true on the 5th argument then that means 1 through 4 must be false.

So to output "Baby Boomers", that means it flagged as false on the first one (year MUST be greater than 1943) but is also less than or equal to 1959. That logic follows through the rest of them.

Nat

Ok! Thanks for taking the time to explain it like this, much appreciated.

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.