# Return value if date falls within range

Options
✭✭✭✭✭

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?

Tags:

## Best Answers

• ✭✭✭✭✭✭
Answer ✓
Options

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

• ✭✭✭✭✭✭
Answer ✓
Options

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

• ✭✭✭✭✭✭
Answer ✓
Options

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

• ✭✭✭✭✭
Options

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?

• ✭✭✭✭✭✭
Answer ✓
Options

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.

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!