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

Thank you.

Best Answer


  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    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! 🙂

  • 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!