IF/AND for date range and TODAY help

ejclaus

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

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

@ejclaus

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

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

@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.