0

EXCEL COUNT IFS Code dates and between times.

Example of Image with Formula: Image with Formula

  1. I have a range called dates IN (xx/xx/xxxx) in column A and dates OUT in column C.
  2. Time IN (XX:XX) in column B and Time OUT in column D.
  3. My entry date is in column E.

How do I build countifs in column G:AB by hour 8-9,9-10 etc., looking at the Date in Column E and count times between, the Time IN and Time OUT using the date range?

Richard Erickson
  • 2,568
  • 8
  • 26
  • 39
Mikey
  • 1
  • 1

1 Answers1

0

The nature of your worksheet Date In, and Date Out can span multiple days, so that would make your CountIf formula quite complex. Since you have to combine A and B, as well as C and D, to get a full understanding of what date its referring to.

You should just create a custom VBA function otherwise its going to get very convoluted using excel's native functions.

Step 1) Change row 1 column G to 7:00:00 AM, then extend it to column AD (all the way to 6:00:00 AM.

Step 2) Hit Alt + F11 and insert a new module on the left hand side. Copy and paste this code:

Public Function CountTimeSlots(ByVal DateIn As Range, ByVal TimeIn As Range, _
                               ByVal DateOut As Range, ByVal TimeOut As Range, _
                               ByVal DateMatch As Range, ByVal TimeMatch As Range) As Integer
    Dim start As Date
    Dim finish As Date
    Dim match As Date
    Dim i As Integer

    'Initialize to 0
    CountTimeSlots = 0

    For i = 1 To DateIn.Rows.Count
        'Combine Column A and B into a singular date
        start = CDate(DateIn.Cells(i, 1).Value) + CDate(TimeIn.Cells(i, 1).Value)
        'Combine Column C and D into a singular date
        finish = CDate(DateOut.Cells(i, 1).Value) + CDate(TimeOut.Cells(i, 1).Value)
        'Combine Column E and the vertical time stamp; Note these Match values are hardcoded to one cell
        match = CDate(DateMatch.Cells(1, 1).Value) + CDate(TimeMatch.Cells(1, 1).Value)

        'If the match value falls between the In and Out time, increment by 1
        If (match >= start And match <= finish) Then
            CountTimeSlots = CountTimeSlots + 1
        End If
    Next i
End Function

Step 3) Use the formula in the cell G2. Then extend it to the right and down (or down then right).

=CountTimeSlots($A$2:$A$7,$B$2:$B$7,$C$2:$C$7,$D$2:$D$7,$E2,G$1)

Step 4) I recommend you swapping columns E and F, or remove column F. Its not very intuitive at first glance that the date in column E is separate from columns A-D.

Kris B
  • 436
  • 2
  • 3