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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

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