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!
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
✅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!
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.
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
Needing the same formula. Anyone have an idea on how we could do it?
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))))
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
@Genevieve P. this works, but all of my "done" line items appear as 99% instead of 100%. Any thoughts?
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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
@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
Hi @Kayla Q
I'm glad to hear you got a working version! 🙂
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
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!
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?
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
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!!!
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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
@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!
Haha no problem! I'm glad we could get there in the end. 🙂
Thanks for the virtual snacks!
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
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!)
