Calculating percent complete based on work days

[leighsha101]

Hello,

I am trying to sort out how to determine what percent of a project is complete. I can figure out calculating the percent of days between two dates using this formula: =(DATEDIF([@[Start Date ]], TODAY(),"d")+1)/(DATEDIF([@[Start Date ]], [@[Due Date ]], "d")+1). But, this doesn't account for weekends. I know how to find number of days excluding weekends using the NETWORKDAYS.INTL. What I can't figure out, is how to make these calculations together. How do I determine what percent of a project is done, based on today's date, but excluding any weekend days.

Please help!

 

[leighsha101]

were you looking for a VBA solution or a formula solution?

I am an infant in terms of Excel use... I am unsure what VBA is. I was able to get it to calculate properly and provide the data I need. My new issue is that it continues to calculate after the due date, because the formula is calculating with Today().

Example - one of the tasks completed yesterday. As of yesterday, the projected % complete was 100%, as it should be. Today, it now shows 125%. How do I alter this =NETWORKDAYS.INTL([@[Start Date ]], TODAY(),1) so that it stops when due date arrives? This formula is calculating work days worked. I am also using =NETWORKDAYS.INTL([@[Start Date ]],[@[Due Date ]],1) to calculate work days planned for task. The percent complete is then calculated with the number for days worked divided by total work days.

 

[leighsha101]

I am an infant in terms of Excel use... I am unsure what VBA is. I was able to get it to calculate properly and provide the data I need. My new issue is that it continues to calculate after the due date, because the formula is calculating with Today().

Example - one of the tasks completed yesterday. As of yesterday, the projected % complete was 100%, as it should be. Today, it now shows 125%. How do I alter this =NETWORKDAYS.INTL([@[Start Date ]], TODAY(),1) so that it stops when due date arrives? This formula is calculating work days worked. I am also using =NETWORKDAYS.INTL([@[Start Date ]],[@[Due Date ]],1) to calculate work days planned for task. The percent complete is then calculated with the number for days worked divided by total work days.

1 is =NETWORKDAYS.INTL([@[Start Date ]],[@[Due Date ]],1)
2 is =NETWORKDAYS.INTL([@[Start Date ]], TODAY(),1)
3 is =O4/N4

 

[awoohaw]

then look at post #8. It is a formula you just copy down the column.

 

[awoohaw]

In post number 8 you enter your date you are "testing" for in the 1st row, not the Today() function.