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

  • Thread starter Thread starter mavgn
  • Start date Start date
M

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.
 
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
 
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,,))
 
The following formula should count the number of records with times from
6:00pm through 6:59pm:

=SUMPRODUCT((HOUR(D3:D1500)>=18)*(HOUR(D3:D1500)<19))

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

=SUMPRODUCT((HOUR(D4:D1501)>=18)*(HOUR(D4:D1501)<19)*(MINUTE(D3:D1500)>0))

Hope this helps,

Hutch
 
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,
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.
 
Back
Top