# Thread: Count the number of workers by 15 minute interval Thanks:  1 Post #5333871 (1) Likes: 0

1. ## Count the number of workers by 15 minute interval

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
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
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.

2. ## Re: Count the number of workers by 15 minute interval

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

3. ## Re: Count the number of workers by 15 minute interval

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.

4. ## Re: Count the number of workers by 15 minute interval

You're welcome and thanks for the feedback.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•