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?
Best Answers

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", "")))))

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

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", "")))))

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?

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.

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

Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!