# How to count Am and PM in the date

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

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))
``````
• 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

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

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
``````
• 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