% Complete Formula based on Start/Finish Dates

Anthony Soyak

Hello!

I am in need of help writing a formula to auto-calculate the "% Complete" column based on the "Start" column date and "Finish" column date. Additionally, I also have an auto column for "Duration". The goal is to have the % Complete auto calculate based on the today's date in relation to the start and finish date columns.

Thank you in advance!

Andrée Starå

Hi @Anthony Soyak

Can you use the automated dependency feature?

https://help.smartsheet.com/articles/765737-project-sheet-columns-start-date-end-date-duration-complete-and-predecessors

Would that work?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Stacey Carrasco

No that didn't help @Andrée Starå

I too am looking for a formula to automatically calculate based on today's date in relation to the start and finish date columns.

Jason Dueck

Needing the same formula. Anyone have an idea on how we could do it?

Genevieve P.

Hi @Stacey Carrasco & @Jason Dueck

Here's another post where Paul provided a formula for this.

Note that you'll need to swap out the "FINISH" and "START" to be your own column names. I would also suggest swapping the row number (2, in this instance) with the @row function:

=IF((ROUND((MIN(TODAY(), FINISH@row) - START@row + 1) / SUM((FINISH@row - START@row + 1) / 100))) <= 0, "Not Started", (ROUND((MIN(TODAY(), FINISH@row) - START@row + 1) / SUM((FINISH@row - START@row + 1) / 100))))

Kayla Q

@Genevieve P. this works, but all of my "done" line items appear as 99% instead of 100%. Any thoughts?

Genevieve P.

Hi @Kayla Q

Try adding an IF statement at the beginning to look and see if the Stage is "Done", and then mark it as 100%!

=IF(Stage@row = "Done", 1, IF((ROUND((MIN(TODAY(), [End Date]@row) - [Start Date]@row + 1) / SUM(([End Date]@row - [Start Date]@row + 1) / 100))) <= 0, "Not Started", (ROUND((MIN(TODAY(), [End Date]@row) - [Start Date]@row + 1) / SUM(([End Date]@row - [Start Date]@row + 1) / 100)))))

Cheers,

Genevieve

Kayla Q

@Genevieve P. I got a version of this to work.

Here is what I landed on:

=(IF(Stage@row = "Done", 100, IF((ROUND((MIN(TODAY(), [End Date]@row) - [Start Date]@row + 1) / SUM(([End Date]@row - [Start Date]@row + 1) / 100))) <= 0, 0, (ROUND((MIN(TODAY(), [End Date]@row) - [Start Date]@row + 1) / SUM(([End Date]@row - [Start Date]@row + 1) / 100)))))) / 100

Genevieve P.

Hi @Kayla Q

I'm glad to hear you got a working version! 🙂

Jeff Casto

Hi gang!

I used this formula (and thank you!!!)

=IF((ROUND((MIN(TODAY(), FINISH@row) - START@row + 1) / SUM((FINISH@row - START@row + 1) / 100))) <= 0, "Not Started", (ROUND((MIN(TODAY(), FINISH@row) - START@row + 1) / SUM((FINISH@row - START@row + 1) / 100))))

However, I'm getting a funny "divide by zero" note and the % in the % complete is incorrect, since all the dates are in the past, the % complete should be 100%, no?

Any further assistance is greatly appreciated and if you're sick of this question...I understand!

Thanks for anything!

Genevieve P.

Hi @Jeff Casto

You'll see an error message if the formula is looking at blank date cells. Try adding an IF statement at the very beginning if you want to get rid of this:

=IF(Finish@row = "", "N/A", IF((ROUND((MIN(TODAY(), Finish@row) - Start@row + 1) / SUM((Finish@row - Start@row + 1) / 100))) <= 0, "Not Started", (ROUND((MIN(TODAY(), Finish@row) - Start@row + 1) / SUM((Finish@row - Start@row + 1) / 100)))))

In regards to your percent, I'm unable to replicate what you're seeing with those dates and this formula. You're right! It should show 100.

Can you post a screen capture with the formula open in your sheet, like so?

Jeff Casto

Holy wow @Genevieve P. ! We are getting close! "Divided by Zero" has been replaced by N/A which is amazing!

I'm still getting the incorrect percentages, so here is the screenshot of the code in the cell.

THANK YOU!!!

Genevieve P.

Hi @Jeff Casto

I just tested with Project Settings enabled and dependencies, and realized this has to do with how the formula is reading the Date cells! It's taking into account your working hours for those days.

I know this looks a little complicated, but all I did to fix this was to wrap each date cell reference in a DATEONLY() function so it simply looks at the date:

=IF(Finish@row = "", "N/A", IF((ROUND((MIN(TODAY(), DATEONLY(Finish@row)) - DATEONLY(Start@row) + 1) / SUM((DATEONLY(Finish@row) - DATEONLY(Start@row) + 1) / 100))) <= 0, "Not Started", (ROUND((MIN(TODAY(), DATEONLY(Finish@row)) - DATEONLY(Start@row) + 1) / SUM((DATEONLY(Finish@row) - DATEONLY(Start@row) + 1) / 100)))))

Cheers,

Genevieve

Jeff Casto

@Genevieve P. ! No way! Jiminy Cricket!!! It works! You're a genius. Thank you soooo much! I wish I could send you a doughnut or something!

Genevieve P.

Haha no problem! I'm glad we could get there in the end. 🙂

https://media.giphy.com/media/g0delle0tz7AH4nXex/giphy.gif

Thanks for the virtual snacks!

Jeff Casto

Haha! Yum!

I have a dumb/random/dumb (that's a dumb random sandwich) question.

Why can't I put a formula in the % Complete column? I made a new column named (don't gag) "Amount Complete" so I could put in a formula.

Does the answer have something to do with these project settings?

Thank you for your time and patience! (If you're sick of my dumb questions, definitely ignore me!)