andrewb90
Well-known Member
- Joined
- Dec 16, 2009
- Messages
- 1,077
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.
Thanks,
Andrew
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
