Formula for Status

danf76

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(Cancelled@row = 1, "Cancelled", IF([On Hold]@row = 1, "On Hold", "Open"))))

Kelly Moore

Hey @danf76

Try this

=IF(ISDATE([Complete Date]@row), "Complete", IF(TODAY()>[End Date]@row, "Overdue", IF(Cancelled@row = 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

Kelly Moore

Hey @danf76

Try this

=IF(ISDATE([Complete Date]@row), "Complete", IF(TODAY()>[End Date]@row, "Overdue", IF(Cancelled@row = 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

danf76

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