Return value if date falls within range

Nat

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?

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.

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

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.

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.

Nat

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

Paul Newcome

Happy to help. 👍️