Countifs on dates and between times

0
votes

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?

1
exceldatetimecountif
What language are you using? Is this an Excel Spreadsheet? Also, please include a MCVE. – Richard Erickson
This is excel, and I included a link to the file. Thank you for your help. :) – Mikey
Please don't post links to download services.I don't want to download these files in case of malware. Include a snippet of your data in the question. – Code Different
I added your picture to the post. Once my edit is reviewed and approved, it will appear. – Richard Erickson

1 Answers

0
votes

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.