Formula for Status

Hello,

I am trying to create a formula to automate the 'status' row in my project grid in SmartSheet. Here is what I am looking for:

Actual start date = Blank, show "Not Started"

If Actual Start Date = is a date and Actual end date = Blank, show "In Progress"

If Actual start date = blank and projected start date > today OR if Actual end date = blank and projected end date > today, show "Overdue"

Actual end date = is a date, show "Complete"

Can anyone help with the nested formula to cover all scenarios?

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Try something like this...

=IF([Actual End Date]@row <> "", "Complete", IF([Actual Start Date]@row = "", IF([Projected Start Date]@row < TODAY(), "Overdue", "Not Started"), IF([Projected End Date]@row < TODAY(), "Overdue", "In Progress")))

All comments

Paul Newcome

Try something like this...

Morgan Stokes

Thank you!! That worked. One further question...is there a way for the formula to show nothing in the event a date is not yet entered? From screenshot below - for tasks I don't yet have dates in, the formula auto-populates 'overdue'. Is there a way for it to be blank until a date is entered under projected start date?

Paul Newcome

Here you go...

=IF([Projected Start Date]@row <> "", IF([Actual End Date]@row <> "", "Complete", IF([Actual Start Date]@row = "", IF([Projected Start Date]@row < TODAY(), "Overdue", "Not Started"), IF([Projected End Date]@row < TODAY(), "Overdue", "In Progress"))))

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.