how to calculate percentage complete with dates

[ivie189gmail]

Can someone please provide a formula to display percent complete by date ranges below?

[TABLE="width: 360"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]Start Date[/TD]
[TD]Finish Date[/TD]
[TD]% Complete[/TD]
[/TR]
[TR]
[TD]Phase duration[/TD]
[TD]02/08/16[/TD]
[TD]02/19/16[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Phase 1[/TD]
[TD]02/08/16[/TD]
[TD]02/10/16[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Phase 2[/TD]
[TD]02/08/16[/TD]
[TD]02/12/16[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Phase 3[/TD]
[TD]02/08/16[/TD]
[TD]02/16/16[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Phase 4[/TD]
[TD]02/08/16[/TD]
[TD]02/19/16[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Welcome to Mr EXcel

Maybe (dates dd/mm/yyyy)


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD]

Start Date​

[/TD]
[TD]

End Date​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Phase duration​

[/TD]
[TD]

08/02/2016​

[/TD]
[TD]

19/02/2016​

[/TD]
[TD]

%Duration​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Phase 1​

[/TD]
[TD]

08/02/2016​

[/TD]
[TD]

10/02/2016​

[/TD]
[TD]

18,18%​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Phase 2​

[/TD]
[TD]

08/02/2016​

[/TD]
[TD]

12/02/2016​

[/TD]
[TD]

36,36%​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Phase 3​

[/TD]
[TD]

08/02/2016​

[/TD]
[TD]

16/02/2016​

[/TD]
[TD]

72,73%​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Phase 4​

[/TD]
[TD]

08/02/2016​

[/TD]
[TD]

19/02/2016​

[/TD]
[TD]

100,00%​

[/TD]
[/TR]
</tbody>[/TABLE]


Formula in D3 copied down
=(C3-B3)/(C$2-B$2)

format % two decimal places

Hope this helps

M.

 

[Steve_R]

Hi
Welcome to the forum.

Assuming you mean percentage by days available not work completed, here are two options. Column D assumes every day is a workday. Column E allows for Mon-Fri only work.
If you need a variation for public holidays there is another parameter networkdays uses for that purpose
or if you need to account for different days off, look up networkdays.intl

Excel 2010

	A	B	C	D	E
Start Date	Finish Date	% Complete	Using Mon-Fri only
Phase duration
Phase 1
Phase 2
Phase 3
Phase 4

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]8/02/2016[/TD]
[TD="align: right"]19/02/2016[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]8/02/2016[/TD]
[TD="align: right"]10/02/2016[/TD]
[TD="align: right"]18.18%[/TD]
[TD="align: right"]30.00%[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]8/02/2016[/TD]
[TD="align: right"]12/02/2016[/TD]
[TD="align: right"]36.36%[/TD]
[TD="align: right"]50.00%[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]8/02/2016[/TD]
[TD="align: right"]16/02/2016[/TD]
[TD="align: right"]72.73%[/TD]
[TD="align: right"]70.00%[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]8/02/2016[/TD]
[TD="align: right"]19/02/2016[/TD]
[TD="align: right"]100.00%[/TD]
[TD="align: right"]100.00%[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D3[/TH]
[TD="align: left"]=(C3-B3)/($C$2-$B$2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E3[/TH]
[TD="align: left"]=NETWORKDAYS(B3,C3)/NETWORKDAYS($B$2,$C$2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[ivie189gmail]

Welcome to Mr EXcel

Maybe (dates dd/mm/yyyy)


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD]

Start Date​

[/TD]
[TD]

End Date​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Phase duration​

[/TD]
[TD]

08/02/2016​

[/TD]
[TD]

19/02/2016​

[/TD]
[TD]

%Duration​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Phase 1​

[/TD]
[TD]

08/02/2016​

[/TD]
[TD]

10/02/2016​

[/TD]
[TD]

18,18%​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Phase 2​

[/TD]
[TD]

08/02/2016​

[/TD]
[TD]

12/02/2016​

[/TD]
[TD]

36,36%​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Phase 3​

[/TD]
[TD]

08/02/2016​

[/TD]
[TD]

16/02/2016​

[/TD]
[TD]

72,73%​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Phase 4​

[/TD]
[TD]

08/02/2016​

[/TD]
[TD]

19/02/2016​

[/TD]
[TD]

100,00%​

[/TD]
[/TR]
</tbody>[/TABLE]


Formula in D3 copied down
=(C3-B3)/(C$2-B$2)

format % two decimal places

Hope this helps

M.

Thank you!

 

[ivie189gmail]

Thank you!