RAG status based on dates overdue

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

Find more posts tagged with

Formulas

Accepted answers

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:

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.