conitif doesn't recognise time format? how to get round this?

[mavgn]

i am tring to get excel to count the number of entries of certain times and a
worksheet. but the format is time eg 06:15 and i want it to look for the
number of entries from "06". can anyone help.
=countif(mon!$D$3:$D$1500,"06*") is what i have got to so far but its because
the details that it is looking at are in time format. I dont want to have to
manualy change all the times to text which would work but takes to long.

 

[David Biddulph]

You could use
=COUNTIF(mon!$D$3:$D$1500,">"&6/24)-COUNTIF(mon!$D$3:$D$1500,">="&7/24) or
=COUNTIF(mon!$D$3:$D$1500,">"&TIME(6,,))-COUNTIF(mon!$D$3:$D$1500,">="&TIME(7,,))I am assuming that you have only times in the cells, not dates and timestogether merely formatted as times.--David Biddulph"mavgn" <[email protected]> wrote in messageam tring to get excel to count the number of entries of certain times anda> worksheet. but the format is time eg 06:15 and i want it to look for the> number of entries from "06". can anyone help.> =countif(mon!$D$3:$D$1500,"06*") is what i have got to so far but itsbecause> the details that it is looking at are in time format. I dont want to haveto> manualy change all the times to text which would work but takes to long.

 

[mavgn]

i am looking for the number of entries between eg 06:00 and 07:00 or 06:59.
">" is grater than but i need between

 

[David Biddulph]

So presumably you have changed ">" to ">=" ?

 

[mavgn]

i am newish at excel and am still learning. i have changed that but it gives
me "06/08/1902 00:00" as an answer? i change that to genral format and it
changes to 949 which i know is way over what i should be looking for
=COUNTIF(mon!$D$3:$D$1500,">="&TIME(6,,))-COUNTIF(mon!$D$3:$D$1500,">="&TIME(7,,))

 

[David Biddulph]

If you're struggling to debug your formula, split it up into manageable
chunks.
=COUNTIF(mon!$D$3:$D$1500,">="&TIME(6,,)) gives you the number greater than
or equal to 06:00
=COUNTIF(mon!$D$3:$D$1500,">="&TIME(7,,)) gives you the number greater than
or equal to 07:00
Which of those gives a number different from what you expected?

 

[Tom Hutchins]

The following formula should count the number of records with times from
6:00pm through 6:59pm:

=SUMPRODUCT((HOUR(D31500)>=18)*(HOUR(D31500)<19))

If you don't want to include 6:00pm itself, try this version:

=SUMPRODUCT((HOUR(D41501)>=18)*(HOUR(D41501)<19)*(MINUTE(D31500)>0))

Hope this helps,

Hutch

 

[mavgn]

i need the result to be numbers/times >= to 06:00 but not more than 07:00.
i am going through 24hrs worth of data and want to break it up into scan's
per 1hr

 

[David Biddulph]

You didn't answer my question.
--
David Biddulph

i need the result to be numbers/times >= to 06:00 but not more than 07:00.
i am going through 24hrs worth of data and want to break it up into scan's
per 1hr

 

[mavgn]

david,
it seems it is counting everything after the 06:00 or 07:00 which is
what i tried to say on my last message.
Tom i have also tried your reply i have never used sumproduct before and
have had results within what i am looking for. i am still having some
difficulties but i have to go now till tomorrow afternoon.

thank you both for your help so far.

 

[Tom Hutchins]

I notice the cell references were off in the second formula I posted. Here is
a corrected version:

=SUMPRODUCT((HOUR(D31500)>=18)*(HOUR(D31500)<19)*(MINUTE(D31500)>0))

If you have never used SUMPRODUCT, here is a link to a good introduction to
it:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Hutch

 

[David Biddulph]

Sorry, this is like drawing teeth. We're trying to help you, but we can't
help you if you won't answer the simple questions and if you steadfastly
persist in withholding the information which we'd need to be able to help
you. Hopefully you can sort it out for yourself.