Multiple Nested If statements

[godzilla65]

Hi Team MrExcel...

Minimum Engagement (3) hour formula

Here is my written explanation of how the rule/formula needs to work, that I am struggling with:

I am working with 4 x Shift Rosters, being start and finish times, below I will try to explain the dynamics of the formula.


If the difference between the end of one shift and the start of the next shift is 1 hour or less, then add together the two shift times – if the total is less than 3 hours, cell should show the amount required to make it up to 3 hours.

If the difference between the end of one shift and the start of the next shift is OVER 1 hour (i.e. 1 hour and 1 minute or more), then each shift must be treated separately: if the total of each is less than 3 hours, cell should show the amount required to make each shift up to 3 hours.

Example shifts:

Shift 1
B7 = 04:00 ( Start )
B8= 06:00 ( Finish )

Shift 2
B9=07:00 ( Start )
B10=11:00 ( Finish )

Shift 3
B11=14:00 ( Start )
B12=17:30 ( Finish )

Shift 4
B13= 20:00 ( Start )
B14 = 22:00 ( Finish )

------------------------

Break between Shift I and Shift 2
B9-B8 = 1 hour,

Break between Shift 2 and Shift 3
B11-B10 = 3 hours,

Break between Shift 3 and Shift 4
B13-B12 = 4.5 hours,
------------------------


I am trying to work on a formula in Cell B41 that calculates the following:

As the first break is only 1 hour, we combine the hours for the first shift (B8-B7=2 hours) with the hours from the second shift (B10-B9=4 hours) = total 6 hours. This is over 3 hours therefore no more hours to add. ( ie: 0)

As the second break is above 1 hour, we look at the third shift separately (B12-B11=3.5 hours). No time is required to make this up to 3 hours. ( ie: 0)

As the third break is 4.5 hours, we look at the fourth shift separately as well (B14-B13=2 hours). This is 1 hour less than the required 3 hours min engagement so the cell B41 should show 1 hour.

Sometimes all breaks will be less than 1 hour so all shifts will be added together to determine if the minimum hours are reached.

So my final result based on the above 4 shift scenario is “1” additional hour ( Cell B41)

Hope this helps, let me know if this doesn’t make sense

I started working on nested ifs but could not get any formula working correctly - - appreciate your thoughts, assistance on above.

Cheers Eric,
Brisbane, Australia

 

[onlyadrafter]

Hello,

there must be a better formula but this gives the answer for your example of 1

=IF(IF(B9-B8<=1/24,((B10-B9)+(B8-B7))*24,(B8-B7)*24)<3,3-IF(B9-B8<=1/24,((B10-B9)+(B8-B7))*24,(B8-B7)*24),0)+IF(IF(B11-B10<=1/24,((B12-B11)+(B10-B9))*24,(B10-B9)*24)<3,3-IF(B11-B10<=1/24,((B12-B11)+(B10-B9))*24,(B10-B9)*24),0)+IF(IF(B13-B12<=1/24,((B14-B13)+(B12-B11))*24,(B12-B11)*24)<3,3-IF(B13-B12<=1/24,((B14-B13)+(B12-B11))*24,(B12-B11)*24),0)+IF(IF(B15-B14<=1/24,((B16-B15)+(B14-B13))*24,(B14-B13)*24)<3,3-IF(B15-B14<=1/24,((B16-B15)+(B14-B13))*24,(B14-B13)*24),0)

you will have to test it for other examples.

 

[godzilla65]

Howdy,

Excellent, that formula looks like it took a few minutes to compile, thanks for that it worked in my first test, now to see how it goes with all the possible scenarios

Thanks for that

Cheers Eric