2

I have An excel file where the date is in this format 6/30/17 9:23 AM I have many values in the date column

How can I count the total AM occurrence and total PM occurrence in the date column

6

Use this formula for AM:

=SUMPRODUCT(--(HOUR($A$1:$A$100)<12))

For PM replace < by >=

If your range might contain blank cells and you want to avoid counting them, you can use:

=SUMPRODUCT(ISNUMBER(A1:A100)*(HOUR($A$1:$A$100)<12))
2
  • Why SUMPRODUCT? and not SUM? where is the 2nd arg? – user1016274 Jul 22 '17 at 14:43
  • @user1016274 SUMPRODUCT with one argument behaves just like SUM but in array formulas it doesn't need to press Ctrl+Shift+Enter. – A.S.H Jul 22 '17 at 14:46
4

You can use an array formula with the SUM and HOUR functions to perform the calculation. E.g., assuming the timestamps are stored in cells A1 through A100, you could use the formulas below:

AM     =SUM((HOUR(A1:A100)<12)*1)
PM     =SUM((HOUR(A1:A100)>11)*1)

After entering the formulas, hit control-shift-enter, i.e., hit those three keys simultaneously. The formulas will then be displayed as shown below:

AM     {=SUM((HOUR(A1:A100)<12)*1)}
PM     {=SUM((HOUR(A1:A100)>11)*1)}

Example of counting AM and PM values in an Excel worksheet

4

Try these two User Defined Functions:

Public Function KountPM(rng As Range) As Long
    Dim r As Range, s As String
    For Each r In rng
        s = r.Text
        If Len(s) > 1 Then
            If Right(s, 2) = "PM" Then KountPM = KountPM + 1
        End If
    Next r
End Function

Public Function KountAM(rng As Range) As Long
    Dim r As Range, s As String
    For Each r In rng
        s = r.Text
        If Len(s) > 1 Then
            If Right(s, 2) = "AM" Then KountAM = KountAM + 1
        End If
    Next r
End Function
2
  • Your "COUNTIF" formulas will only work if the cell contains only the time, not the time and date as the original question requires. – Rob Gale Jul 23 '17 at 2:54
  • @RobG you are correct. I will fix my answer. – Gary's Student Jul 23 '17 at 2:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.