Countif time syntax

  • Thread starter Thread starter RJG
  • Start date Start date
R

RJG

I have a worksheet with time entries (formatted as date/time cells) and
I want to count how many entries are between certain hours - i.e. how
many are from 1p to 2p, from 2p to 3p etc.
I understand the syntax as =countif(range,criteria)

I am having trouble figuring out how to enter the criteria for the time
range
i.e. countif(d2:d366,>13:00<14:00)does not work,
nor does countif(d2:d366,">13:00<14:00")

can anybody help with the syntax?

Thanks
RG
 
Hi RG

Countif can't handle two criteria. Do it this way:

=COUNTIF(D2:D366,>13/24)-COUNTIF(D2:D366,>=14/24)

(If cells cotains time AND date, this will not work. Everything is >14/24
then.)

You can also do this very easily with a Pivot table. Date-Time row labels
can be grouped by hour (and almost any unit).

HTH. Best wishes Harald
 
Hi RJG,
supposing you hv the data in column D, with heading as Time. Enter following
in Column E1,E2,E3
Time
2:31:00 PM
Click to expand...
<3:00:00 PM
Now use the following formula:-
=DCOUNTA(D:D,"Time",E1:E3)
Note: Time can be displayed in any relevant format but Excel accepts it
generally in the form which we have declared in Column E.
--
Click on Yes, if it is useful.

Thanks & Best Regards,
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India
 
Typo alert:
=COUNTIF(D2:D366,">"&13/24)-COUNTIF(D2:D366,">="&14/24)
or
=COUNTIF(D2:D366,">"&TIME(13,0,0))-COUNTIF(D2:D366,">="&TIME(14,0,0))
 
For x = 1 to 100
MsgBox "1 Coffee!!!"
MsgBox "2 Write code."
Next

Thanks Dave!

Best wishes Harald
 
Thanks all - these worl perfectly ( and thanks for the tip about
removing the date info - both were included in the field - I still did
not have the syntax right, but this would have tripped me up once I did)
RG
 
Back
Top