RAG status based on dates overdue

BenM

Hi,

I'm attempting to get an output of either, "On Track", "Overdue" or "Critically Overdue" for baseline milestone dates in Smartsheet, with the Overdue being 5 days over & Critically overdue being 20 days over respectively.

I have drafted the initial IF statement, however i'm suck on how to add the additional "Critically Overdue" parameter and how to add the 5 & 20 days threshold.

=IF([Baseline Date]@row = [Actual Date]@row, "On Track", "Overdue")

Any suggestions would be appreciated!

Ben

Bassam Khalil

Hi @BenM 

Hope you are fine, please try the following formula and covert it to column format formula:

=IFERROR(IF(OR([Actual Date]@row = "", [Baseline Date]@row = ""), "", IF([Actual Date]@row
= [Baseline Date]@row, "On Track", IF(AND([Baseline Date]@row - [Actual Date]@row >= 5,
[Baseline Date]@row - [Actual Date]@row <= 20), "Overdue",
IF([Baseline Date]@row - [Actual Date]@row > 20, "Critically Overdue")))), "")

The following screenshot shows the result:

Bassam Khalil

Hi @BenM 

Hope you are fine, please try the following formula and covert it to column format formula:

=IFERROR(IF(OR([Actual Date]@row = "", [Baseline Date]@row = ""), "", IF([Actual Date]@row
= [Baseline Date]@row, "On Track", IF(AND([Baseline Date]@row - [Actual Date]@row >= 5,
[Baseline Date]@row - [Actual Date]@row <= 20), "Overdue",
IF([Baseline Date]@row - [Actual Date]@row > 20, "Critically Overdue")))), "")

The following screenshot shows the result:

BenM

Hi Bassam,

That has worked perfectly, thank you!😀

Kind regards,

Ben

Bassam Khalil

@BenM

Excellent, i will be happy to help you any time.