Add and Subtracting Time & Date within Defined Ranges

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]
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Forum statistics

Threads
1,225,739
Messages
6,186,741
Members
453,370
Latest member
juliewar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top