Hi Rob!

I've been playing with this for some time and am coming to the same
conclusions as you.

In my view, the data might need re-arranging so that each day is dealt
with separately so as to avoid the problems of spanning midnight. It's
not that it can't be done. But the solution is very complicated.

May I ask as an aside what is the underlying purpose of the splitting
up of the times? It may be that a more simple approach can achieve the
same results.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Rob van Gelder" wrote in
message ...
After having a very big think about your problem, it's much more
complicated
than I first thought.

You have a start and end checking time range, call them cS, cE
You also have a start and end event time range, call them eS, eE

Consider also that an event may start before midnight and end after
midnight, but you are not dealing with dates. So it would appear the
event
finished before it ended. eg. 23:30 to 02:30.

I've detected the following combinations in which the times may be
ordered,
along with the outcome:
eS, eE, cS, cE = 0
eE, eS, cS, cE = cE-cS
eS, cS, eE, cE = eE-cS
eE, cS, eS, cE = cE-eS
eS, cS, cE, eE = cE-cS
eE, cS, cE, eS = 0
cS, eS, eE, cE = eE-eS
cS, eE, eS, cE = eE-cS + cE-eS
cS, eS, cE, eE = cE-eS
cS, eE, cE, eS = eE-cS
cS, cE, eS, eE = 0
cS, cE, eE, eS = cE-cS

So your formula for the first line could look like: if eS < eE and
eE < cS
and cS < cE then 0, else.....
There's also opportunity to simplify since some of the outcomes are
the same
(eg. = 0)

Hope this helps,

Rob



"Jay" wrote in message
...
One Problem down, one to go. The formula has trouble with
anytime around midnight. I get a negative number. Any
suggestions.

Thanks for your help
Jay