Results 1 to 4 of 4

Thread: Count the number of workers by 15 minute interval

  1. #1
    Board Regular
    Join Date
    Nov 2003
    Location
    Sydney, Australia
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile 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.
    Thanx
    Marklarbear

  2. #2
    Board Regular jmacleary's Avatar
    Join Date
    Oct 2015
    Location
    at a desk in the UK
    Posts
    621
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Last edited by jmacleary; Aug 29th, 2019 at 05:05 AM.
    If my answer has helped, please vote using the Thanks or Like buttons on the left.
    John

  3. #3
    Board Regular
    Join Date
    Nov 2003
    Location
    Sydney, Australia
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    Thanx
    Marklarbear

  4. #4
    Board Regular jmacleary's Avatar
    Join Date
    Oct 2015
    Location
    at a desk in the UK
    Posts
    621
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count the number of workers by 15 minute interval

    You're welcome and thanks for the feedback.
    If my answer has helped, please vote using the Thanks or Like buttons on the left.
    John

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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