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
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this communityI 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))
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)}
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