Using Countif to count occurences of time?

L

Liketoknow

Hi..i am trying to get totals from data and am using 2 different times to
count from (eg. i am trying to find how many cells contain times in the 6:00
am to 2:30 pm range in the same column...I can't seem to be able to count
these correct

Any help is appreciated
 
S

Stefi

Try this solution:

=SUMPRODUCT(--(A2:A6>TIMEVALUE("6:00")),--(A2:A6<TIMEVALUE("14:30")))

Regards,
Stefi

„Liketoknow†ezt írta:
 
S

Stefi

I forgot to tell you that A2:A7 is only an example, change it as necessary!
Stefi


„Liketoknow†ezt írta:
 
L

Liketoknow

Thanks..i will try it

Stefi said:
I forgot to tell you that A2:A7 is only an example, change it as necessary!
Stefi


„Liketoknow†ezt írta:
 
L

Liketoknow

Stefi...thanks for the help...works great but i do have 1 problem..when i try
and count from 22:30 pm to 6:00 am i get 0 ...any ideas?
i have checked all the formula and it is exactly as the other (with time
changes of course

tks
 
S

Stefi

Yes, because XL stores time values as a fraction, e.g. 6:00 is stored as 0.25
(that is 6/24), 22:30 as 0.9375 (that is 22.5/24), and XL doesn't know that
you mean start time 22:30 toDAY, but end time 6:00 toMORROW, unless you
specify it explicitely. One way of specifying it is using date + time, e.g.:
If
A2: today's date 22:30
A3: today's date 23:30
A4: tomorrow's date 2:30
A5: tomorrow's date 3:30
A6: tomorrow's date 6:30

then in A7

=SUMPRODUCT(--(A2:A6>TODAY()+TIMEVALUE("22:30")),--(A2:A6<TODAY()+1+TIMEVALUE("6:00")))

returns the correct answer, that is 3.

Regards,
Stefi



„Liketoknow†ezt írta:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top