Using Countif to count occurences of time?
 

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

Using Countif to count occurences of time?
 

Try this solution:

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

Regards,
Stefi

€žLiketoknow€� ezt Ã*rta:

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

Using Countif to count occurences of time?
 

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


€žLiketoknow€� ezt Ã*rta:

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

Using Countif to count occurences of time?
 

Thanks..i will try it

"Stefi" wrote:

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


€žLiketoknow€� ezt Ã*rta:

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

Using Countif to count occurences of time?
 

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

"Liketoknow" wrote:

Thanks..i will try it

"Stefi" wrote:

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


€žLiketoknow€� ezt Ã*rta:

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

Using Countif to count occurences of time?
 

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:A6TODAY()+TIMEVALUE("22:30")),--(A2:A6<TODAY()+1+TIMEVALUE("6:00")))

returns the correct answer, that is 3.

Regards,
Stefi



€žLiketoknow€� ezt Ã*rta:

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

"Liketoknow" wrote:

Thanks..i will try it

"Stefi" wrote:

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


€žLiketoknow€� ezt Ã*rta:

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