Formula for Status

danf76danf76
edited 01/31/21 in Formulas and Functions
01/31/21 Edited 01/31/21
Accepted

I would like to auto create a status based on an end date, completed date, cancelled checkbox or on hold checkbox. If end date is in the future an neither checkbox is selected then "Open". If the end date is in the past then "Overdue". If complete date has been entered then "Complete". If cancelled checked then "Cancelled". If On Hold checked then "On Hold". This is what I have so far with no luck.

=IF(ISDATE([Complete Date]@row), "Complete", IF([due due]@row > today (), "Complete", IF([email protected] = 1, "Cancelled", IF([On Hold]@row = 1, "On Hold", "Open"))))

Best Answer

  • KDMKDM ✭✭✭✭✭
    Accepted Answer

    Hey @danf76

    Try this

    =IF(ISDATE([Complete Date]@row), "Complete", IF(TODAY()>[End Date]@row, "Overdue", IF([email protected] = 1, "Cancelled", IF([On Hold]@row = 1, "On Hold", "Open"))))

    The second term seemed it might be mistaken? I didn't include it in this formula.

    IF([due due]@row > today (), "Complete" (If correct, it should be written as IF([due due]@row> TODAY(), "Complete" )

    Please note that column name references are both spelling and case-sensitive.

    Please advise on the second term

    cheers,

    Kelly

Answers

  • KDMKDM ✭✭✭✭✭
    Accepted Answer

    Hey @danf76

    Try this

    =IF(ISDATE([Complete Date]@row), "Complete", IF(TODAY()>[End Date]@row, "Overdue", IF([email protected] = 1, "Cancelled", IF([On Hold]@row = 1, "On Hold", "Open"))))

    The second term seemed it might be mistaken? I didn't include it in this formula.

    IF([due due]@row > today (), "Complete" (If correct, it should be written as IF([due due]@row> TODAY(), "Complete" )

    Please note that column name references are both spelling and case-sensitive.

    Please advise on the second term

    cheers,

    Kelly

  • That did it, thank you and yes that was indeed a mistake. Have a great evening and thanks again!

Sign In or Register to comment.