I need some help with a calculation. I am hoping you can help.

I need to have a column calculate the status of a project based on where today stands compared to its % complete and where we are between the projected start and finish date.

As an example. This is what is entered manually.

Start End % complete Project status

01/07/24 22/11/24 92% In Progress - On Track

Today is Oct 18th and the %complete is 92% I would say that the project status is correct and should remain as In Progress - On Track.

But if today was November 25th and the % complete is less than 100% the project status should be In Progress - Delayed

The other project status options are ”Not started” and “Closed”

Find more posts tagged with

Accepted answers

jmyzk_cloudsmart_jp

Hi @TracyLAshton

You can use a formula to calculate the project status based on the current date, percentage complete, and start and end dates. Here’s a formula approach that covers all four statuses:

"Not Started": If today's date is before the start date.
"In Progress - On Track": If today's date is between the start and end dates, and the % complete is less than 100%.
"In Progress - Delayed": If today's date is past the end date and the % complete is less than 100%.
"Closed": If the % complete is 100%.

Assuming you have columns named "Start," "Finish," and "% Complete," and you want the "Status" column to update automatically, use this formula:

=IF(TODAY() < Start@row, "Not Started", IF([% Complete]@row = 1, "Closed", IF(TODAY() > Finish@row, "In Progress - Delayed", "In Progress - On Track")))

Explanation:

=TODAY() < Start@row: Checks if today's date is before the start date, marking the project as "Not Started."
[% Complete]@row = 1: Checks if the project is 100% complete (using the decimal format for %), setting the status to "Closed."
TODAY() > Finish@row: Checks if today's date is past the end date, marking the project as "In Progress - Delayed."
Otherwise, it defaults to "In Progress - On Track."

https://app.smartsheet.com/b/publish?EQBCT=df68dd1a9ebe49bdbeb70da9704692c6

All comments

jmyzk_cloudsmart_jp

Hi @TracyLAshton

You can use a formula to calculate the project status based on the current date, percentage complete, and start and end dates. Here’s a formula approach that covers all four statuses:

"Not Started": If today's date is before the start date.
"In Progress - On Track": If today's date is between the start and end dates, and the % complete is less than 100%.
"In Progress - Delayed": If today's date is past the end date and the % complete is less than 100%.
"Closed": If the % complete is 100%.

Assuming you have columns named "Start," "Finish," and "% Complete," and you want the "Status" column to update automatically, use this formula:

=IF(TODAY() < Start@row, "Not Started", IF([% Complete]@row = 1, "Closed", IF(TODAY() > Finish@row, "In Progress - Delayed", "In Progress - On Track")))

Explanation:

=TODAY() < Start@row: Checks if today's date is before the start date, marking the project as "Not Started."
[% Complete]@row = 1: Checks if the project is 100% complete (using the decimal format for %), setting the status to "Closed."
TODAY() > Finish@row: Checks if today's date is past the end date, marking the project as "In Progress - Delayed."
Otherwise, it defaults to "In Progress - On Track."

https://app.smartsheet.com/b/publish?EQBCT=df68dd1a9ebe49bdbeb70da9704692c6

TracyLAshton

@jmyzk_cloudsmart_jp you are a genius. Thank you so very much, this is perfect.

jmyzk_cloudsmart_jp

Happy to help!😁 @TracyLAshton

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.