Hi,

I have a table of times (in text format) with two columns.

Example:

Col A Col B
0:15 03:30
09:00 14:00
15:00 22:39
23:15 03:45

The first time-pair (0.15-03.30) is on the same day. The last (23.15 - 03.45) is spread to two different days.

I need to count the number of rows where Col B value is between for example 03-04 BUT only if they are in the same day (if col A < Col B).
So in the example table I would get results of 1 (even tho there are 2 instanses where col B has the value between 03-04)
And naturally I need another formula to count the instances where the COL B value is between 03-04 BUT only if they time spreads to two different days (Col A > Col B).

Any Ideas... been trying countif, count with If, sumproduct.. but haven't come up with a working formula. I'd like to avoid macros, but if it is the only way - then those are ok too.

- FinExcel -

Try this for A < B:
This for A > B:
If you want to put the high and low time values in cells, you can change them easier than using TIME(3,0,0) and TIME(4,0,0).

Regards,

Ax

Hey,

For some reason the formula didn't work at first (must've misspelled somewhere), but after toying around with it I got it working perfectly.

Thank you very much - now that I got it working I can go have a nice weekend without having to worry about this... cheers to that

- FinExcel -

Excellent! Glad to hear it.