Calculate planned % complete

Harley Esguerra

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.

bisaacs

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!

Harley Esguerra

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.

bisaacs

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