# Calculate planned % complete

Hello,

I could not find this anywhere so hoping someone can help. I need to calculate the planned % complete based on the total duration of my project and elasped days. I want to exclude weekends and CDN and USA holidays. We do not track buydget so I am trying to get around calculating using the real earned value formulas. Any help would be greatly appreciated.

• ✭✭✭✭✭

Hey @Harley Esguerra,

This is doable! First you'll want to create a Date helper column that has a list of all CDN and USA holidays that I'd just label "Holidays" (we'll need this later). Then, in your % Complete column, enter this formula:

`=(NETWORKDAYS([Start Date]@row, TODAY(), Holidays:Holidays) / NETWORKDAYS([Start Date]@row, [End Date]@row, Holidays:Holidays))`

This will divide the current # of business days elapsed between the start date and today with the total # of business days for the total project time. Each of the NETWORKDAYS function in this calculation are excluding the dates listed in the Holidays column we created earlier from the total # of days.

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 responding! Is there a way to create a helper column for the % Complete as well? I need to use the existing % Complete to compare the planned % Complete data for my dashboard. Also, how would you populate the "Holidays" column with the dates? I tried this and I got anm unparseable error.

• ✭✭✭✭✭

Hey @Harley Esguerra,

Just to clarify, you want to separate % Complete columns? One for the planned (aka, ideal) % complete track vs what the current (aka actual) % complete is?

Unfortunately there isn't a built in running list of Holidays to my knowledge, however there is a way to have them added in the Admin Center so that they work across systems:

Designate Holidays for Multiple Years — Smartsheet Community

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!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!