Calculating % of completion based on start & End dates

TPALJA

I'm looking for a formula that calculates the % of completion based on start and end dates

Jason Tarpinian

A simple way to do it would be like below. It just compares today to the start, and the total duration of the task, and divides. The MIN/MAX are there so that if the task hasn't started yet, instead of a negative you get 0%, and if the task end is in the past, it doesn't go past 100%.

=MAX(0, MIN(1, NETDAYS(Start@row, TODAY()) / NETDAYS(Start@row, End@row)))

TPALJA

@Jason Tarpinian thank you! would I use networkdays to calculate business days?

Jason Tarpinian

@TPALJA Yup, use NETWORKDAYS to omit weekends and holidays that you've set up!

TPALJA

@Jason Tarpinian I want to add a step and leave the column blank if the start and end date are blank, is that possible?

Hollie Green

Yes add an If(And statement to it.

=If(And(Start@row="",End@row=""),"",MAX(0, MIN(1, NETDAYS(Start@row, TODAY()) / NETDAYS(Start@row, End@row))))