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

TracyLAshton

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”

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:

1. "Not Started": If today's date is before the start date.
2. "In Progress - On Track": If today's date is between the start and end dates, and the % complete is less than 100%.
3. "In Progress - Delayed": If today's date is past the end date and the % complete is less than 100%.
4. "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

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:

1. "Not Started": If today's date is before the start date.
2. "In Progress - On Track": If today's date is between the start and end dates, and the % complete is less than 100%.
3. "In Progress - Delayed": If today's date is past the end date and the % complete is less than 100%.
4. "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