% Complete Question

Options

Hi, I am fairly new to SmartSheet and been looking for help, but can't seem to find what I am looking for, I have tried a similar formula I saw on here, but I can't get it to work.

I need a simple formula to show how far along a project is in %, based on dates or no of days.

I have fudged it for now with a traditional excel formula but it's not a long term fix and I have no doubt given myself more work.

I have a start date / finish date / today date / duration of project / number of days into the project.

But I also want it to show % completed and if the project hasn't started I need it to say "Not Started".

Can anyone help me please?

Answers

  • bisaacs
    bisaacs ✭✭✭✭✭
    Options

    Hey @Caroline Dunnell,

    Something like this might work:

    =IF([Start Date]@row = "", "Not Started", NETWORKDAY(TODAY(), [End Date]@row) / NETWORKDAY([Start Date]@row, TODAY()))

    This formula only calculates based on business days and doesn't include weekends. If you want it to include weekends you can use this:

    =IF([Start Date]@row = "", "Not Started", NETDAYs(TODAY(), [End Date]@row) / NETDAYs([Start Date]@row, TODAY()))

    Hope this helps!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • Thanks for your help - I have tried this and it has 397% for a project that should be 19.7%". This is what I put in based on the column titles

    =IF([MOBILISATION DATE]@row ="", "Not Started", NETDAYs(TODAY(),[ESTIMATION COMPLETION DATE]@row)/NETDAYs([MOBILISATION DATE]@row,TODAY()))

    Did I get something wrong?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!