Calculating Time Error

[andrewb90]

Hello All,

I am running a formula that will count the number of employees scheduled at a particular time. (Each formula references a different hour). In addition to what is an incredibly long formula, I now have discovered that if a shift is scheduled to end past 23:59, then the entire shift will not be counted.
Can somebody assist me in fixing this, and if you have any hints of shortening this formula, that would be greatly appreciated.

Code:
=(SUM(IF(E$87:E$111<$B87,IF(F$87:F$111>$A87,IF(F$87:F$111<$B87,F$87:F$111,$B87)-IF(E$87:E$111>$A87,E$87:E$111,$A87))))*24)+(SUM(IF(E$113:E$137<$B87,IF(F$113:F$137>$A87,IF(F$113:F$137<$B87,F$113:F$137,$B87)-IF(E$113:E$137>$A87,E$113:E$137,$A87))))*24)+(SUM(IF(E$139:E$146<$B87,IF(F$139:F$146>$A87,IF(F$139:F$146<$B87,F$139:F$146,$B87)-IF(E$139:E$146>$A87,E$139:E$146,$A87))))*24)+(SUM(IF(E$148:E$156<$B87,IF(F$148:F$156>$A87,IF(F$148:F$156<$B87,F$148:F$156,$B87)-IF(E$148:E$156>$A87,E$148:E$156,$A87))))*24)
Thanks,

Andrew

 

[joeu2004]

The problem is that if a shift runs past midnight (00:00) then the formulas completely ignore the entire shift

The problem arises because you (should) count shifts that start before the time period and end after the time period. Shifts ending after midnight do meet both conditions.

Try one of the following solutions.

[TABLE="class: grid, width: 460"]
<tbody>[TR]
[TD]
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Solution #1
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Joe
[/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"]14:00[/TD]
[TD]
[/TD]
[TD="align: right"]14:00[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Sara[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"]21:00
[/TD]
[TD]
[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Bob[/TD]
[TD="align: right"]18:00
[/TD]
[TD="align: right"]0:30[/TD]
[TD]
[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Sam[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]23:30[/TD]
[TD]
[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]18:00[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Chad[/TD]
[TD="align: right"]14:00[/TD]
[TD="align: right"]19:00[/TD]
[TD]
[/TD]
[TD="align: right"]18:00[/TD]
[TD="align: right"]19:00[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]19:00[/TD]
[TD="align: right"]20:00[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

Enter the following formula into G2, and copy down:

=COUNTIFS($B$2:$B$6, "<=" & E2, $C$2:$C$6, ">=" & F2)

When shifts start before midnight, the key is to enter after-midnight times as a formula, like the following in C4:

=1 + "0:30"

Note the double-quotes around the actual time. All time cells are formatted as Custom hh:mm.

[TABLE="class: grid, width: 460"]
<tbody>[TR]
[TD]
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Solution #2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Joe[/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"]14:00[/TD]
[TD]
[/TD]
[TD="align: right"]14:00[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Sara[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"]21:00[/TD]
[TD]
[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Bob[/TD]
[TD="align: right"]18:00[/TD]
[TD="align: right"]0:30[/TD]
[TD]
[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Sam[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]23:30[/TD]
[TD]
[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]18:00[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Chad[/TD]
[TD="align: right"]14:00[/TD]
[TD="align: right"]19:00[/TD]
[TD]
[/TD]
[TD="align: right"]18:00[/TD]
[TD="align: right"]19:00[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]19:00[/TD]
[TD="align: right"]20:00[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

Array-enter (press ctrl+shift+Enter instead of just Enter) the following formula into G2, and copy down:

=SUM(IF($B$2:$B$6<=E2, IF($C$2:$C$6+($C$2:$C$6<$B$2:$B$6)>=F2, 1)))

Excel displays the array-entered formula with curly braces around it.

In this case, all times are entered as they appear. In particular, C4 is simply 0:30.

Note that this approach works only for shifts of less than 24 hours.

 

[joeu2004]

PS for solution #2....

Array-enter (press ctrl+shift+Enter instead of just Enter) the following formula into G2, and copy down:
=SUM(IF($B$2:$B$6<=E2, IF($C$2:$C$6+($C$2:$C$6<$B$2:$B$6)>=F2, 1)))

Better.... Normally-enter (just press Enter as usual) the following formula into G2, and copy down:

=SUMPRODUCT(($B$2:$B$6<=E3)*($C$2:$C$6+($C$2:$C$6<$B$2:$B$6)>=F3))

In this context, the multiplication (*) performs an AND operation.

 

[andrewb90]

[QUOTE

When shifts start before midnight, the key is to enter after-midnight times as a formula, like the following in C4:

=1 + "0:30"

Note the double-quotes around the actual time. All time cells are formatted as Custom hh:mm.

.[/QUOTE]

The only issue that I have is making the user type in a formula. I'm trying to make it as computer illiterate as necessary.

 

[joeu2004]

The only issue that I have is making the user type in a formula. I'm trying to make it as computer illiterate as necessary.

Understandable. I anticipated that. That's why I offered the SUMPRODUCT alternative as well (solution #2, modified by my "PS" follow-up). That does not require any special input.

PS: But hopefully the 1+"0:30" approach helps you understand what we are accomplishing with the SUMPRODUCT alternative. We are simply adding 1 (1 day; 24 hours) in the formula instead of in the data.