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