STATUS FORMULA

Hi Community,

I always struggle with the status formulas. There are 5 status points:

  1. Not started - identified by the [DATE HA WAS ASSIGNED] (I want it so if the cell is blank it results as Not Started).
  2. In Progress - HA - identified by the [DATE HA WAS ASSIGNED] (I want it so if there is a date entered the status changes to In Progress - HA).
  3. In Progress - IT - identified by the [DATE HA SENT TO IT DEPT. FOR ACCESS] (I want it so if there is a date entered the status changes to In Progress - IT).
  4. In Progress - Orientation - identified by [DATE AD ACCOUNT PROVIDED] (I want it so if there is a date entered the status changes to In Progress - Orientation.
  5. Complete - identified by [HA ADDED SECTION 5 IN-PERSON DOCUMENTS TO SS].

I have the status column set up as a dropdown selection. This is the last piece I need; I am so close!

I do prefer the @row formulas :)

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @HardWork

    I assumed your process would proceed linearly. If it doesn't you may have to define additional criteria to delineate the correct status.

    Try this

    =IF(ISDATE([HA ADDED SECTION 5 IN-PERSON DOCUMENTS TO SS]@row), "Complete", IF(ISDATE([DATE AD ACCOUNT PROVIDED]@row), "In Progress - Orientation", IF(ISDATE([DATE HA SENT TO IT DEPT. FOR ACCESS]@row), "In Progress - IT", IF(ISDATE([DATE HA WAS ASSIGNED]@row), "In Progress - HA", IF(AND(ISDATE([Requestor Auto Date]@row), [DATE HA WAS ASSIGNED]@row = ""), "Not Started")))))

    Will this formula work for you?

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @HardWork

    I assumed your process would proceed linearly. If it doesn't you may have to define additional criteria to delineate the correct status.

    Try this

    =IF(ISDATE([HA ADDED SECTION 5 IN-PERSON DOCUMENTS TO SS]@row), "Complete", IF(ISDATE([DATE AD ACCOUNT PROVIDED]@row), "In Progress - Orientation", IF(ISDATE([DATE HA SENT TO IT DEPT. FOR ACCESS]@row), "In Progress - IT", IF(ISDATE([DATE HA WAS ASSIGNED]@row), "In Progress - HA", IF(AND(ISDATE([Requestor Auto Date]@row), [DATE HA WAS ASSIGNED]@row = ""), "Not Started")))))

    Will this formula work for you?

    Kelly

  • Kelly, you are a lifesaver! Thank you so very much. Yes, it was a linear process. This works perfectly!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!