IF/AND for date range and TODAY help

I'm looking to return phase names based on if today's date falls within specific time ranges on another smartsheet.

For example: I have start and end columns/dates on one sheet for "Phase 1," "Phase 2," and "Phase 3" for different projects. On another sheet, I'd like to return "Phase 1," "Phase 2," or "Phase 3" if TODAY falls within one of the 3 phases in the first sheet, or return "Not started" if TODAY falls before Phase 1 start date, "Complete" if TODAY falls after Phase 3 end date.



  • red1998
    red1998 ✭✭

    I think the best option is a combination of several IF formulas.

    =IF(TODAY(0) < {PHASE 1 START DATE}, "Not Started", IF(TODAY(0) < {PHASE 1 END DATE}, "Phase 1", IF(TODAY(0) < {PHASE 2 END DATE}, "Phase 2", IF(TODAY(0) < {PHASE 3 END DATE}, "Phase 3", "Complete"))))

    Let me know if this works for you!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 08/03/22


    Create an automation that runs every morning at 1am to record the date in the CurrentDate field on rows where the Project is any value. (This updates the CurrentDate daily, whereas the TODAY function only updates if the sheet gets opened.)

    Status column:

    =IF(CurrentDate@row < [Phase1Start]@row, "Not Started", IF(AND(CurrentDate@row >= [Phase1Start]@row, CurrentDate@row <= [Phase1End]@row), "Phase 1", IF(AND(CurrentDate@row >= [Phase2Start]@row, CurrentDate@row <= [Phase2End]@row), "Phase 2", IF(AND(CurrentDate@row >= [Phase3Start]@row, CurrentDate@row <= [Phase3End]@row), "Phase 3", "Completed"))))

    Then on your status sheet- Project column, Status Column - for each Project row:

    In the Status column:

    =INDEX({Reference to Status column in Phase Dates sheet}, MATCH(Project@row, {Reference to Status column in Phase Dates sheet}, 0))


    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • ejclaus
    ejclaus ✭✭

    thanks for your input @red1998 and @Jeff Reisman

    I tried your formula @red1998 which worked great, however, what if the phases were not sequential and had inactive time between phases?

    For example, if there were 3 months between Phase 2 and 3? Can I include a between date range in the formula?

  • red1998
    red1998 ✭✭

    @ejclaus Yes I believe so.

    =IF(TODAY(0) < {PHASE 1 START DATE}, "Not Started", IF(AND(TODAY(0) >= {PHASE 1 START DATE}, TODAY(0) <= {PHASE 1 END DATE}), "Phase 1", IF(AND(TODAY(0) >= {PHASE 2 START DATE}, TODAY(0) <= {PHASE 2 END DATE}), "Phase 2", IF(AND(TODAY(0) >= {PHASE 3 START DATE}, TODAY(0) <= {PHASE 3 END DATE}), "Phase 3", "Complete"))))

    Let me know if that is what you mean.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!