Countif time syntax

[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

 

[Harald Staff]

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

 

[DILipandey]

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.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India

 

[Dave Peterson]

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

 

[Harald Staff]

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

Thanks Dave!

Best wishes Harald

 

[Dave Peterson]

You would make Bart Simpson's punishment too easy!

 

[RJG]

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