Count the number of workers by 15 minute interval

Marklarbear

Board Regular
Joined
Nov 6, 2003
Messages
64
Office Version
  1. 365
Platform
  1. Windows
H All,

I’ve broken my brain on excel and need assistance…..

I’ve built a doc to help me calculate the number of staff working by 15 minute interval per day of the week. I have around 50 staff but they all work different days & hours in the day. I seem to be having issues at certain times of the day in the calculations in my spreadsheet.

I do a data dump (from an outside source) into excel into a range of cells… to simplify it a bit, assume I dump the following (1 staff member as an example):

Tab 1

Start times into column A - ie cell A1 will have value 19/08/2019 9:00:00 AM
Stop times into column B - ie cell B1 will have value 19/08/2019 6:00:00 PM
(cells are formatted as category custom, type d/mm/yyyy h:mm)

I have the following formulas cells A5 & B5 (to remove the dates from the start and stop times):

Cell A5: =A1-INT(A1)
Cell B5: =B1-INT(B1)

The data in cell A5 shows as 9:00
The data in cell B5 shows as 18:00
(cells are formatted as category Time, type 13:30)


Tab 2

In Tab 2, I have a range of cells that counts the number of staff working by 15 minute intervals based on the data in Tab 1:

Starting at cell C3 and going across to cell H3, I list the shift number:

C3 = 1
D3 = 2
E3 = 3
F3 = 4

And so on


Starting at cell C4 and going across to cell H4, I reference the start times from Tab 1:

C4 =’tab 1’!A5

And so on

Starting at cell C5 and going across to cell H5, I reference the stop times from Tab 1:

C5 =’tab 1’!B5

And so on

Cells C3, C4, C5 return the following values:

1
8:00
17:00

(rows 4 & 5 are formatted as category custom, type h:mm)


In cell C6 I have the following formula:

=if(‘Tab 1’!A5>0,1,””)

This formula returns the value of 1 if there is a start time in Tab 1 cell A5. If there’s no value in Tab 1 cell A5 it returns nothing.




From here is where the magic starts…..

Still in Tab 2…

From cell B8 to cell B103 I have every 15 minute interval of the day listed ie

B8 = 12:00:00 AM
B9 = 12:15:00 AM
B10 = 12:30:00 AM

And so on, to cell B103 = 11:45:00 PM

(all rows are formatted as category custom, type h:mm)

In cell C8 I have the following formula:

=IF(AND($B8>=C$4,$B8<C$5),C$6,"")

This returns a value of 1 if the start time is equal to or greater than midnight AND the stop time is less than midnight.

(all rows are formatted as category general)

The formula in cell C8 is repeated all the way down to cell C103. The only change is to the reference to cells in column B.


Therefore the formula in cell C44 should show as:

=IF(AND($B44>=C$4,$B44<C$5),C$6,"")

Which will return the value of 1 (as the start time for shift 1 is 9:00am).

Cell C45 will return a value of 1 and so on until cell C76. Cell C76 will return no value as the shift finish’s at 5pm (which is correct for what I want it to do).


This is repeated for shift 2 in column D, shift 3 in column E and so on…..


The issue I have:

For whatever reason the following times return no value (they should return a 1): 2:00 AM, 5:00 AM, 8:00 AM, 11:00 AM. Also…

For whatever reason the following times return value of 1 (they should return no value): 2:00 PM, 5:00 PM, 8:00 PM, 11:00 PM.


So if I have a shift that starts at 8:00 AM, the first return of the value 1 is in the 8:15 AM interval – the first return of the value 1 should be in the 8:00 AM interval.

Same thing at the other end of the shift…. If I have a shift that finish’s at 8:00 PM, the last return of the value 1 should be in the preceding interval at 7:45 PM.

All other shift start times / stop times return the correct values… it’s just ): 2:00 AM, 5:00 AM, 8:00 AM, 11:00 AM, 2:00 PM, 5:00 PM, 8:00 PM, 11:00 PM.

(it seems coincidental that they are all 3 hours apart??)


If I manually type in the start / stop times into the data dump section in Tab 1 everything works fine….. but that defeats the purpose of having this “automated”…. And also why would it be that the data dump works fine for a shift that starts at 7:59 AM and at 8:01 AM but not for a shift that start at 8:00 AM ?

Any assistance would be greatly appreciated.
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,056
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Hi there. Your problem arises from the fact that excel doesn't handle decimal values precisely. If you look at the table below, you will see that some fractions of a day can be calculated exactly, but others are an approximation (e.g. 3am is calculated by excel as 0.083333333 whereas in fact it is 0.08333 recurring. The formatted value shows 3am, but the 'raw' value is fractionally out, enough to upset your calculation. To resolve it, round all your values to the same number of decimal places.

Excel 2007 32 bit
E
F
G
H
9
formatted time'raw' time
10
29/08/2019 00:00​
12:00:00 AM​
0​
11
29/08/2019 01:00​
02:00:00 AM​
0.041666667​
12
29/08/2019 02:00​
03:00:00 AM​
0.083333333​
13
29/08/2019 03:00​
04:00:00 AM​
0.125​
14
29/08/2019 04:00​
05:00:00 AM​
0.166666667​
15
29/08/2019 05:00​
06:00:00 AM​
0.208333333​
16
29/08/2019 06:00​
07:00:00 AM​
0.25​
17
29/08/2019 07:00​
08:00:00 AM​
0.291666667​
18
29/08/2019 08:00​
09:00:00 AM​
0.333333333​
19
29/08/2019 09:00​
10:00:00 AM​
0.375​
20
29/08/2019 10:00​
11:00:00 AM​
0.416666667​
21
29/08/2019 11:00​
12:00:00 PM​
0.458333333​
22
29/08/2019 12:00​
01:00:00 PM​
0.5​
23
29/08/2019 13:00​
02:00:00 PM​
0.541666667​
24
29/08/2019 14:00​
03:00:00 PM​
0.583333333​
25
29/08/2019 15:00​
04:00:00 PM​
0.625​
26
29/08/2019 16:00​
05:00:00 PM​
0.666666667​
27
29/08/2019 17:00​
06:00:00 PM​
0.708333333​
28
29/08/2019 18:00​
07:00:00 PM​
0.75​
29
29/08/2019 19:00​
08:00:00 PM​
0.791666667​
30
29/08/2019 20:00​
09:00:00 PM​
0.833333333​
31
29/08/2019 21:00​
10:00:00 PM​
0.875​
32
29/08/2019 22:00​
11:00:00 PM​
0.916666667​
33
29/08/2019 23:00​
12:00:00 AM​
0.958333333​
34
30/08/2019 00:00​
12:00:00 AM​
0​
Sheet: Sheet1
 
Last edited:

Marklarbear

Board Regular
Joined
Nov 6, 2003
Messages
64
Office Version
  1. 365
Platform
  1. Windows
Thanks heaps John.... you set me on the right path... With some slight modifications to a couple of formulas and inserting the TRUNC function to restrict the decimal places to 3, I was able to get to where I needed..... Wouldn't have gotten there without your assistance. :)
 

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,056
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
You're welcome and thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,863
Messages
5,627,332
Members
416,241
Latest member
moggy84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top