MichaelRSnow
Active Member
- Joined
- Aug 3, 2010
- Messages
- 409
Hi, I need some help and I don't know where to start with this one?
I'm trying to build a resource plan, tasks take specific amount of time and I want to add or subtract this time to existing times to calculate the date they would be processed on? My date range would have non working days (Saturdays & Sundays and non working hours, 6pm to 8am), is this possible with excel
i.e. If...
Task 1 was scheduled to complete by 5pm on Fri 5th Jan
Task 2 (which had a dependency on Task 1) takes 2 hours, it would complete at 9am on Mon 8th Jan
Task 3 (which had a dependency on Task 2) take 30 minutes, it would complete at 9.30am on Mon 8th Jan
I can create a annual table with working hours and working days which it can lookup, but don't know where to start with the formula?
Table as per below
[TABLE="width: 368"]
<tbody>[TR]
[TD]DAY[/TD]
[TD]START TIME[/TD]
[TD="colspan: 3"]FINISH TIME[/TD]
[/TR]
[TR]
[TD]FRI[/TD]
[TD]05/01/2018 00:09:00[/TD]
[TD="colspan: 3"]05/01/2018 00:17:00[/TD]
[/TR]
[TR]
[TD]SAT[/TD]
[TD]06/01/2018 00:00:00[/TD]
[TD="colspan: 3"]06/01/2018 00:00:00[/TD]
[/TR]
[TR]
[TD]SUN[/TD]
[TD]07/01/2018 00:00:00[/TD]
[TD="colspan: 3"]07/01/2018 00:00:00[/TD]
[/TR]
[TR]
[TD]MON[/TD]
[TD]08/01/2018 00:09:00[/TD]
[TD="colspan: 3"]08/01/2018 00:17:00[/TD]
[/TR]
</tbody><colgroup><col><col><col><col span="2"></colgroup>[/TABLE]
I'm trying to build a resource plan, tasks take specific amount of time and I want to add or subtract this time to existing times to calculate the date they would be processed on? My date range would have non working days (Saturdays & Sundays and non working hours, 6pm to 8am), is this possible with excel
i.e. If...
Task 1 was scheduled to complete by 5pm on Fri 5th Jan
Task 2 (which had a dependency on Task 1) takes 2 hours, it would complete at 9am on Mon 8th Jan
Task 3 (which had a dependency on Task 2) take 30 minutes, it would complete at 9.30am on Mon 8th Jan
I can create a annual table with working hours and working days which it can lookup, but don't know where to start with the formula?
Table as per below
[TABLE="width: 368"]
<tbody>[TR]
[TD]DAY[/TD]
[TD]START TIME[/TD]
[TD="colspan: 3"]FINISH TIME[/TD]
[/TR]
[TR]
[TD]FRI[/TD]
[TD]05/01/2018 00:09:00[/TD]
[TD="colspan: 3"]05/01/2018 00:17:00[/TD]
[/TR]
[TR]
[TD]SAT[/TD]
[TD]06/01/2018 00:00:00[/TD]
[TD="colspan: 3"]06/01/2018 00:00:00[/TD]
[/TR]
[TR]
[TD]SUN[/TD]
[TD]07/01/2018 00:00:00[/TD]
[TD="colspan: 3"]07/01/2018 00:00:00[/TD]
[/TR]
[TR]
[TD]MON[/TD]
[TD]08/01/2018 00:09:00[/TD]
[TD="colspan: 3"]08/01/2018 00:17:00[/TD]
[/TR]
</tbody><colgroup><col><col><col><col span="2"></colgroup>[/TABLE]