How to calculate 8-hours work day and Finish Date for Excel Project Plan Tracker

Question

I have this example created in Microsoft Project, but I want to make the same using Microsoft Excel.
Need help in calculation of hours and calculating next date based on 8-hours day.
Like, if you see in "Sub-Series 1" Task 1 to Task 4 especially Task 3 of Sub-Series 1.
The duration for Task 3 is 4hrs and the 'Finish Date' changes to next day date, as it sums up with the hours for previous tasks (Task 1 & Task 2) as well, as the sum is >8 so the 'Finish Date' changes to next date.
Please help in figuring out the Formulae to achieve duration in 'days' and 'Finish Date' for 'Sub-Series' & for 'Series 1'.
Manual Inputs: Duration and Start date for 'Tasks' of each 'Sub-Series'
Formulae required for: 'Finish date' and sum of 'Duration' into days
Thanks in advance and best regards,

Qadeer · Answer

I'm able to achieve the desired outputs by the help of 3 helper columns. if anyone have better solution please do share it. Thanks!

F5 cell is manual input for Start date.

H5 contains =IF(J5=0,F5+J5,IF(AND(J5=1,K5=0),(F5+J5)-1,IF(AND($J5<>$J4,K5>0),WORKDAY($F5,($J5-$J4)),IF(AND($J5=$J4,K5>0),$F5,IF(AND($J6<>$J5,K5>0),(F5+J5)-1,$F5)))))

G5 contains =IF(OR(WEEKDAY(H5)={1,7}),WORKDAY(H5,1),IF(J5=0,F5+J5,IF(AND(J5=1,K5=0),(F5+J5)-1,IF(AND($J5<>$J4,K5>0),WORKDAY($F5,($J5-$J4)),IF(AND($J5=$J4,K5>0),$F5,IF(AND($J6<>$J5,K5>0),(F5+J5)-1,$F5))))))

F6 contains =IF(OR(WEEKDAY(H5)={1,7}),WORKDAY(H5,1),IF(AND($I6<>$I5,$K5=0),$H5+1,$H5))

I6 contains running SUM =SUM(I5,E6) and Cell J5 =QUOTIENT(I5,8) and Cell K5 =MOD(I5,8)

Thanks everyone! & @PeterH

PeterH · Answer

Excel has built in templates for many types of file.
And even template gantt charts.
Go to file -> and search for a gantt chart.

How to calculate 8-hours work day and Finish Date for Excel Project Plan Tracker

2 Answers

Add your own answers!

Ask a Question