# % Complete Formula based on Start/Finish Dates

Options

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.

Tags:
«1

• ✭✭✭✭✭✭
Options

Can you use the automated dependency feature?

Would that work?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

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.

Senior Program Coordinator

De Anza College

• Options

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

• Employee
Options

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))))

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭
Options

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

• Employee
Options

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

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭
edited 01/25/23
Options

@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

• Employee
Options

Hi @Kayla Q

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

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭
Options

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!

• Employee
Options

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?

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭
Options

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!!!

• Employee
Options

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

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭
Options

@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!

• Employee
Options

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

Thanks for the virtual snacks!

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭
Options

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!)

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!