[SOLVED] 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

Try this solution:

=SUMPRODUCT(--(A2:A6>TIMEVALUE("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

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

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

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

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:

> 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