Check if a date is between 2 dates.
I have two sheets :
I wanna check in which range (StartDate  EndDate) the date (i.e. 01/23/24 above) is in the sheet below and then get the value from the primary column (q12024) into the Quarter field above. Any ideas?
Thank you.
Best Answer

Hi @Virgil T.,
You would use an INDEX COLLECT for this. If you're familiar with how cross sheet references work, then it would be something like this in your "Quarter" cell/column:
=INDEX(COLLECT({Primary Column},{StartDate},@cell<=[Date]@row,{EndDate}>=[Date]@row),1)
If you're not sure how to do cross sheet references (shown in the { } brackets) there are some instructions here:
If you have any problems/questions on this, just let us know! 🙂
Answers

Hi @Virgil T.,
You would use an INDEX COLLECT for this. If you're familiar with how cross sheet references work, then it would be something like this in your "Quarter" cell/column:
=INDEX(COLLECT({Primary Column},{StartDate},@cell<=[Date]@row,{EndDate}>=[Date]@row),1)
If you're not sure how to do cross sheet references (shown in the { } brackets) there are some instructions here:
If you have any problems/questions on this, just let us know! 🙂

Thank you very much for the answer. You forgot an @cell 😊 in the formula but I got the idea and it worked 🙏. The formula I used is: =INDEX(COLLECT({quarters: Primary Column}, {quarters: StartDate}, @cell <= Date@row, {quarters: EndDate}, @cell >= Date@row), 1)
Help Article Resources
Categories
Check out the Formula Handbook template!