% Complete based on Todays date, Start and End dates

I'm looking to return a % complete based on the start and end dates and the current date.

So if a task starts on the 17/7 and ends on the 31/7, and today is the 24/7 - the % complete would be 53%. - (I want to have this as a separate column so I can keep dependencies on the actual % complete column)

=(MIN(TODAY(),*finish date*)-*start date*+1)/*finish date*-*start date*+1)

The formula above would work in Excel, but is there a smartsheet alternative? Any help appreciated.

Find more posts tagged with

Manufacturing

Formulas

Project Management

Comments

AGillespie

Ok, I've found a new problem -

This formula works if the task has started/ currently being done :

=(ROUND((MIN(TODAY(), FINISH1) - START1 + 1) / SUM((FINISH1 - START1 + 1) / 100)))

The formula below works for tasks not yet started, would show "Not Started", and blank if the number was above 0.

=IF((ROUND((MIN(TODAY(), FINISH2) - START2 + 1) / SUM((FINISH2 - START2 + 1) / 100))) < 0, "Not Started","")

How do I write a formula to show a number (which would be the possible % complete) when the value is false, and "Not Started" when the value is true.

12vanblart

In your second formula, if you replace the last `""` with the function you want to calculate, it will return a number instead of a blank value.

=IF((ROUND((MIN(TODAY(), FINISH2) - START2 + 1) / SUM((FINISH2 - START2 + 1) / 100))) < 0, "Not Started", (ROUND((MIN(TODAY(), FINISH2) - START2 + 1) / SUM((FINISH2 - START2 + 1) / 100))))

Paul Newcome

=IF((ROUND((MIN(TODAY(), FINISH2) - START2 + 1) / SUM((FINISH2 - START2 + 1) / 100))) <= 0, "Not Started",(ROUND((MIN(TODAY(), FINISH2) - START2 + 1) / SUM((FINISH2 - START2 + 1) / 100))))

Give this a try. If the value is <= 0, it will say "Not Started". Otherwise it will input the value.

AlexK_Emburse

I ended up using this formula, which I like because it requires Start/End dates to be set, and rather than saying "Not Started" I opted for 0% completion.

I feel this logic is simpler and cleaner as well. I'm sure it could be even better, but I'm no developer.

=IF(OR(ISBLANK(Start2), ISBLANK(Finish2)), "Add Dates", IF(TODAY() > Finish2, 1, IF(TODAY() < Start2, 0, ((TODAY() - Start2) / (Finish2 - Start2)))))

Sign in to join the conversation!

Quick Links

Trending discussions

index match using multiple critieria
Hello, I want to use a formula using INDEX/ MATCH Function using multiple criteria to get a single value I am trying to get a value of Column Description from File 2 based on matching Source System ,Table name and Attribute Field Index ( as the column names may be different ) The formula Im using gets me an INVALID…
How can I use Conditional Formatting for date in the past or future when a formula created the date
Hi Everyone, Thank you for your time in reading this. How can I use Conditional Formatting for date in the past or future when a formula created the date? The black columns are different dates generated. The 2 yellow columns are then selecting the relevant date based on different criteria. The white columns are me trying…
Auto Check a box after 24 hours
I have tried to get smartsheet to auto check a box 24 hours after a row is created and column "Task Started" is not checked. =IF(AND([Task Started]@row <> 1, TODAY() - Created@row >= 1), 1) This returns #UNPARSEABLE. Any help would be appreciated.