This is a small part of a larger project. I am not a VBA person so please keep that in mind.
I am trying to develop a task level schedule planner for a 2 shift 10 hour per shift operation. I have a list of task with start date, duration and a calculated end date based on the duration. All task are sequential for this example. There are 2 problems. 1) How to deal with the 4 hours of non-work time? and 2) How to deal with duration's longer than 20 hours?
For the first problem I created the following to calculate the task end time. It works for problem 1 but is kind of a long formula. Any suggestions to simplify it?
A13 = start date
B13 = Duration in hours
B10 = 2nd shift end time
B6 = 1st shift start time
=IF(AND(A13+B13/24-TRUNC(A13+B13/24,0)>$B$10,A13+B13/24-TRUNC(A13+B13/24,0)<$B$7),A13+B13/24+4/24,A13+B13/24)
For problem 2 I am having problems dealing with any duration greater than a workday of 20 hours. My thought is that for every increment of 20 hours I would add 4 hours (20 to 40 add 4 hrs, 40 to 80 add 8 hrs, etc.) to the calculated end time as long as the calculated task end time is not during the non-work time. Any suggestions on how to tackle this problem?
After I get this solved it's time to add non-work days (holidays, weekends). Did I mention that this tool will be used globally with variable holiday, weekends and other local work rules. The fun is only beginning.
I am trying to develop a task level schedule planner for a 2 shift 10 hour per shift operation. I have a list of task with start date, duration and a calculated end date based on the duration. All task are sequential for this example. There are 2 problems. 1) How to deal with the 4 hours of non-work time? and 2) How to deal with duration's longer than 20 hours?
For the first problem I created the following to calculate the task end time. It works for problem 1 but is kind of a long formula. Any suggestions to simplify it?
A13 = start date
B13 = Duration in hours
B10 = 2nd shift end time
B6 = 1st shift start time
=IF(AND(A13+B13/24-TRUNC(A13+B13/24,0)>$B$10,A13+B13/24-TRUNC(A13+B13/24,0)<$B$7),A13+B13/24+4/24,A13+B13/24)
For problem 2 I am having problems dealing with any duration greater than a workday of 20 hours. My thought is that for every increment of 20 hours I would add 4 hours (20 to 40 add 4 hrs, 40 to 80 add 8 hrs, etc.) to the calculated end time as long as the calculated task end time is not during the non-work time. Any suggestions on how to tackle this problem?
After I get this solved it's time to add non-work days (holidays, weekends). Did I mention that this tool will be used globally with variable holiday, weekends and other local work rules. The fun is only beginning.
