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

 

[Chrisdontm]

Could you post a spreadsheet example of how you have it setup, perhaps through Dropbox.com...?

 

[andrewb90]

Here is a basic Example:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[/TR]
[TR]
[TD]Joe
[/TD]
[TD]8:00[/TD]
[TD]14:00[/TD]
[/TR]
[TR]
[TD]Sara[/TD]
[TD]15:00[/TD]
[TD]21:00[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]18:00[/TD]
[TD]00:30[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]17:00[/TD]
[TD]23:30[/TD]
[/TR]
[TR]
[TD]Chad[/TD]
[TD]14:00[/TD]
[TD]19:00[/TD]
[/TR]
</tbody>[/TABLE]

Now my full sheet of course has more employees and 7 days of the week.
My current formula does work (It references other cells as well)
Here is the part that I am looking at:
[TABLE="width: 500"]
<tbody>[TR]
[TD]2:00-3:00[/TD]
[TD]1 Employee Working (Chad)[/TD]
[/TR]
[TR]
[TD]3:00-4:00[/TD]
[TD]2 Employees Working (Chad,Sara)[/TD]
[/TR]
[TR]
[TD]4:00-5:00[/TD]
[TD]2 Employees Working[/TD]
[/TR]
[TR]
[TD]5:00-6:00[/TD]
[TD]3 Employees Working (Sam started his shift)[/TD]
[/TR]
[TR]
[TD]6:00-7:00[/TD]
[TD]Still 3, should be 4 (Bob)[/TD]
[/TR]
[TR]
[TD]7:00-8:00[/TD]
[TD]2, should be 3. Chad is off shift[/TD]
[/TR]
</tbody>[/TABLE]
The problem is that if a shift runs past midnight (00:00) then the formulas completely ignore the entire shift, and I am not sure how to remedy it. That is the problem.

 

[Chrisdontm]

We can get to your desired answer, I must tell you that it will be tomorrow night before I can work on it...
If someone else steps in that will be fine, otherwise I'll help you out...

 

[Chrisdontm]

I am not completely following how you have things setup...
But, let me show you a formula of interest;
Suppose Bob's Start time in cell B4 is: 18:00
and his End time in C4 is just after midnight: 00:30

=MOD(C4-B4,1)*24

The MOD formula leaves you with the remainder after a division problem, ( The number one is what you are dividing it by )
When you take C4-B4 you will get a negative number, but you will be dropping off the integer and left with the portion of a day, times 24 ( Hours ), it will give you 6.5

But you must have the cell formatted as a Number to get the right answer...

I still don't mind helping, I just don't see how it is laid out;
What is in cell B87?
What is being totaled in E87:E111?
What is in A87?

 

[andrewb90]

A87 & B87 are time ranges. Let's say they are 9:00 and 10:00, well, my formula looks at all the rows that I have employees and if their shift is between 9:00&10:00, then a value of 1 is returned for each person (rows 87:111)
So if row 87 had a shift from 8:00 to 14:00, then my formula would calculate that as, 9-10:00 falls in between 8-14:00.
Am I making sense?

 

[FDibbins]

See if this is what you want?

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td]Employee[/td][td]Start Time[/td][td]End Time[/td][td]

2:00:00 PM​

[/td][td]

3:00:00 PM​

[/td][td]

1​

[/td][/tr]

[tr][td]

2​

[/td][td]Joe[/td][td]

8:00:00 AM​

[/td][td]

2:00:00 PM​

[/td][td]

3:00:00 PM​

[/td][td]

4:00:00 PM​

[/td][td]

2​

[/td][/tr]

[tr][td]

3​

[/td][td]Sara[/td][td]

3:00:00 PM​

[/td][td]

9:00:00 PM​

[/td][td]

4:00:00 PM​

[/td][td]

5:00:00 PM​

[/td][td]

2​

[/td][/tr]

[tr][td]

4​

[/td][td]Bob[/td][td]

6:00:00 PM​

[/td][td]

12:30:00 AM​

[/td][td]

5:00:00 PM​

[/td][td]

6:00:00 PM​

[/td][td]

3​

[/td][/tr]

[tr][td]

5​

[/td][td]Sam[/td][td]

5:00:00 PM​

[/td][td]

11:30:00 PM​

[/td][td]

6:00:00 PM​

[/td][td]

7:00:00 PM​

[/td][td]

2​

[/td][/tr]

[tr][td]

6​

[/td][td]Chad[/td][td]

2:00:00 PM​

[/td][td]

7:00:00 PM​

[/td][td]

7:00:00 PM​

[/td][td]

8:00:00 PM​

[/td][td]

2​

[/td][/tr]
[/table]
F1=COUNTIFS($B$2:$B$6,"<="&D1,$C$2:$C$6,">"&E1)
copied down

 

[andrewb90]

Still encountering the same issue where the row with a post midnight ending time does not get calculated.

 

[asad]

Hi,

Try this:
First insert an empty row on top of your data range. Then, if the end time is in Column C, type in time 4:00 and in Column D type in time 24:00. Starting under 24:00, type this formula

=IF(C2 < $C$1,C2+$D$1,C2)

In columns E and F, if you have the time range for calculating number of employees, then in column G use this formula

{=SUM(IF($B$2:$B$6<=E3,IF($D$2:$D$6>=F3,1,0)))}

and DON"T press enter, press ctrl+shift+enter to use it as an array formula. Then drag it down to required range.

 

[andrewb90]

I'm still encountering the same issue, It's skipping the same line.