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!

 

[awoohaw]

Hi, try this:

Book1
	A	B
1	Holiday:	Mon, 2023-03-13
2	Wed, 2023-03-01	8%
3	Thu, 2023-03-02	15%
4	Fri, 2023-03-03	23%
5	Sat, 2023-03-04	23%
6	Sun, 2023-03-05	23%
7	Mon, 2023-03-06	31%
8	Tue, 2023-03-07	38%
9	Wed, 2023-03-08	46%
10	Thu, 2023-03-09	54%
11	Fri, 2023-03-10	62%
12	Sat, 2023-03-11	62%
13	Sun, 2023-03-12	62%
14	Mon, 2023-03-13	62%
15	Tue, 2023-03-14	69%
16	Wed, 2023-03-15	77%
17	Thu, 2023-03-16	85%
18	Fri, 2023-03-17	92%
19	Sat, 2023-03-18	92%
20	Sun, 2023-03-19	92%
21	Mon, 2023-03-20	100%
Sheet1

Cell Formulas
Range	Formula
B2:B21	B2	=NETWORKDAYS.INTL($A$2,$A2,1,$B$1)/NETWORKDAYS.INTL($A$2,$A$21,1,$B$1)

 

[awoohaw]

The above also includes a holiday schedule (The date I chose as a Holiday is just made up.) You can take it out, it is not a required argument.

 

[leighsha101]

Here is what I am working with. The 'Bathroom demo' (highlighted yellow) occurs within the work week (Mon-Wed) and calculates correctly. It started today and we should be 1/3 completed based on it taking 3 days. For 'Tile - shower / floors' (highlighted green), there is a total of 8 days start to end. Two of those days, however, are Saturday and Sunday, which wouldn't count towards percent complete. The 'Projected % Complete' (circled in red) calculates based on start and due date but doesn't account for weekends. So, if I wanted to check on say, 3/28/23, to find out what percent complete the project is, it'll give me the wrong percent because it is calculating we worked on the task two days more than actual.

We are trying to calculate how much of a task/project is done on the current date / today, based only on actual days worked, not total days, between start and due dates. Is this possible? I am not opposed to doing additional side calculations, just unsure what they would be.

My appreciation!

 

[leighsha101]

View attachment 87963
Here is what I am working with. The 'Bathroom demo' (highlighted yellow) occurs within the work week (Mon-Wed) and calculates correctly. It started today and we should be 1/3 completed based on it taking 3 days. For 'Tile - shower / floors' (highlighted green), there is a total of 8 days start to end. Two of those days, however, are Saturday and Sunday, which wouldn't count towards percent complete. The 'Projected % Complete' (circled in red) calculates based on start and due date but doesn't account for weekends. So, if I wanted to check on say, 3/28/23, to find out what percent complete the project is, it'll give me the wrong percent because it is calculating we worked on the task two days more than actual.

We are trying to calculate how much of a task/project is done on the current date / today, based only on actual days worked, not total days, between start and due dates. Is this possible? I am not opposed to doing additional side calculations, just unsure what they would be.

My appreciation!

Edit: The 'Projected % Complete' (circled in red) calculates based on current date within the start and due dates but doesn't account for weekends.

 

[bebo021999]

May be:

=IF(AND(TODAY()>=[@[Start Date]],TODAY()<=[@[Due Date]]),NETWORKDAYS.INTL([@[Start Date]],TODAY(),1)/NETWORKDAYS.INTL([@[Start Date]],[@[Due Date]],1),"")

 

[montecarlo2012]

You can use the NETWORKDAYS.INTL function to calculate the number of workdays between two dates, and then divide that by the total number of workdays in the project to get the percentage complete. Here's an example VBA code that does this:

Sub CalculateProjectProgress()
    Dim startDate As Date
    Dim dueDate As Date
    Dim totalWorkDays As Integer
    Dim completedWorkDays As Integer
    Dim progressPercent As Double
    
    ' Get the start and due dates from your worksheet
    startDate = Range("A1").Value
    dueDate = Range("B1").Value
    
    ' Calculate the total number of workdays in the project
    totalWorkDays = Application.WorksheetFunction.NetworkDays_Intl(startDate, dueDate, 1)
    
    ' Calculate the number of completed workdays
    completedWorkDays = Application.WorksheetFunction.NetworkDays_Intl(startDate, Date, 1)
    
    ' Calculate the progress percentage
    progressPercent = completedWorkDays / totalWorkDays
    
    ' Write the progress percentage to your worksheet
    Range("C1").Value = progressPercent
End Sub

In this example, the start date is in cell A1, the due date is in cell B1, and the progress percentage is written to cell C1. You can adjust the cell references as needed for your worksheet. Note that the NETWORKDAYS.INTL function uses the "1" argument to exclude weekends; you can adjust this argument to exclude other types of holidays if needed.

 

[awoohaw]

View attachment 87963
Here is what I am working with. The 'Bathroom demo' (highlighted yellow) occurs within the work week (Mon-Wed) and calculates correctly. It started today and we should be 1/3 completed based on it taking 3 days. For 'Tile - shower / floors' (highlighted green), there is a total of 8 days start to end. Two of those days, however, are Saturday and Sunday, which wouldn't count towards percent complete. The 'Projected % Complete' (circled in red) calculates based on start and due date but doesn't account for weekends. So, if I wanted to check on say, 3/28/23, to find out what percent complete the project is, it'll give me the wrong percent because it is calculating we worked on the task two days more than actual.

We are trying to calculate how much of a task/project is done on the current date / today, based only on actual days worked, not total days, between start and due dates. Is this possible? I am not opposed to doing additional side calculations, just unsure what they would be.

My appreciation!

I'm pretty sure the formula in post number 2 does that. If not please explain what I have have missed. I had no idea you were thinking of tasks within the project. Does this work better?:

mr excel questions 15.xlsm
	A	B	C	D
1	Current Date:	2023-03-21	Holiday:	Mon, 2023-03-13
2
3	Start Date	Due Date
4	Mon, 2023-03-20	Wed, 2023-03-22	33%
5	Thu, 2023-03-23	Thu, 2023-03-23	0%
6	Fri, 2023-03-24	Fri, 2023-03-31	0%
7	Fri, 2023-03-17	Wed, 2023-03-22	50%
LeighSha101

Cell Formulas
Range	Formula
C4:C7	C4	=IF($A4>$B$1,0,(NETWORKDAYS.INTL($A4,$B$1,1,$D$1)-1)/NETWORKDAYS.INTL($A4,$B4,1,$D$1))

 

[leighsha101]

You can use the NETWORKDAYS.INTL function to calculate the number of workdays between two dates, and then divide that by the total number of workdays in the project to get the percentage complete. Here's an example VBA code that does this:

Sub CalculateProjectProgress()
    Dim startDate As Date
    Dim dueDate As Date
    Dim totalWorkDays As Integer
    Dim completedWorkDays As Integer
    Dim progressPercent As Double
  
    ' Get the start and due dates from your worksheet
    startDate = Range("A1").Value
    dueDate = Range("B1").Value
  
    ' Calculate the total number of workdays in the project
    totalWorkDays = Application.WorksheetFunction.NetworkDays_Intl(startDate, dueDate, 1)
  
    ' Calculate the number of completed workdays
    completedWorkDays = Application.WorksheetFunction.NetworkDays_Intl(startDate, Date, 1)
  
    ' Calculate the progress percentage
    progressPercent = completedWorkDays / totalWorkDays
  
    ' Write the progress percentage to your worksheet
    Range("C1").Value = progressPercent
End Sub

In this example, the start date is in cell A1, the due date is in cell B1, and the progress percentage is written to cell C1. You can adjust the cell references as needed for your worksheet. Note that the NETWORKDAYS.INTL function uses the "1" argument to exclude weekends; you can adjust this argument to exclude other types of holidays if needed.

Yes! Thank you for this!

 

[awoohaw]

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