excelexcel-formulaformula

How do I count the number of times a specific time is between multiple time intervals (see example)

EDIT: Forgot to add the current formula (see bottom of post)

I have multiple columns that store time as interval values (e.g. 10PM stored as 22, 6AM stored as 06) - see image

I need to check if a certain time is between all the different time intervals

How do I write an Excel formula that checks if a time is between multiple time intervals (e.g. is 2:30AM in between 10PM and 4AM?)?

My time intervals are stored in columns:

J5&K5 [i.e. J5 is the start (value: 02) and K5 is the end (value: 12)]
L5&M5
N5&O5
P5&Q5
R5&S5 [i.e. R5 is the start (value: 06) and K5 is the end (value: 18)]

The times I would like to check are in T1:AE5

e.g. T1 = 6:30AM
e.g. U1 = 8:30AM
e.g. AD1 = 02:30AM
e.g. AE1 = 04:30AM

Example (see image): Count the number of times AD1 is in between:

J5&K5 
L5&M5
N5&O5
P5&Q5
R5&S5 (i.e. R5 is the start and K5 is the end)

In this example, the count should be 3 and stored in AD5 (it's in between J5&K5, L5&M5, N5&O5)

sample time intervals and times to be tested

Things to consider:

  • 00:00 and 24:00 are both stored as 24
  • Sometimes the start time is at night (e.g. 22, 24) and the end time is in the morning (e.g. 02, 04, 06)
  • Sometimes the start time is in the morning (e.g. 02 or 04) and the end time is in the afternoon (e.g. 14 or 16)

Current formula in AD1 (which can't account for the fact that 2.5 is actually in between 24 and 06)

=SUMPRODUCT(($AD$1>INDEX(J4:S4,1,N(IF(1,{1,3,5,7,9}))))*($AD$1<INDEX(J4:S4,1,N(IF(1,{2,4,6,8,10})))))

^Note this is a formula provided by another Stackoverflow member when I had a somewhat simpler problem to tackle before the requirement changed

Solution

  • Breaking down the formula you have:

    =SUMPRODUCT(($AD$1>INDEX(J4:S4,1,N(IF(1,{1,3,5,7,9}))))*($AD$1<INDEX(J4:S4,1,N(IF(1,{2,4,6,8,10})))))

    In logical terms:

    AND

    The additional logic required is to count the instances where:

    OR

    WHILE

    This is accomplished with:

    =SUMPRODUCT((($AD$1>INDEX(J4:S4,1,N(IF(1,{1,3,5,7,9}))))+($AD$1<INDEX(J4:S4,1,N(IF(1,{2,4,6,8,10})))))*(INDEX(J4:S4,1,N(IF(1,{1,3,5,7,9})))>INDEX(J4:S4,1,N(IF(1,{2,4,6,8,10})))))

    So to count all the desired situations, you should use the sum of both the original and the new parts:

    =SUMPRODUCT(($AD$1>INDEX(J4:S4,1,N(IF(1,{1,3,5,7,9}))))*($AD$1<INDEX(J4:S4,1,N(IF(1,{2,4,6,8,10})))))+SUMPRODUCT((($AD$1>INDEX(J4:S4,1,N(IF(1,{1,3,5,7,9}))))+($AD$1<INDEX(J4:S4,1,N(IF(1,{2,4,6,8,10})))))*(INDEX(J4:S4,1,N(IF(1,{1,3,5,7,9})))>INDEX(J4:S4,1,N(IF(1,{2,4,6,8,10})))))