# Countif time syntax

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(D2366,>13/24)-COUNTIF(D2366,>=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
<3:00:00 PM
Now use the following formula:-
=DCOUNTA(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.)
New Delhi, India

=COUNTIF(D2366,">"&13/24)-COUNTIF(D2366,">="&14/24)
or
=COUNTIF(D2366,">"&TIME(13,0,0))-COUNTIF(D2366,">="&TIME(14,0,0))

For x = 1 to 100
MsgBox "1 Coffee!!!"
MsgBox "2 Write code."
Next

Thanks Dave!

Best wishes Harald

You would make Bart Simpson's punishment too easy!

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