Check if a date is between 2 dates.

Virgil T.

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 (q1-2024) into the Quarter field above. Any ideas?

Thank you.

Nick Korna

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:

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

If you have any problems/questions on this, just let us know! 🙂

Nick Korna

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:

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

If you have any problems/questions on this, just let us know! 🙂

Virgil T.

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)