I'm not sure what you mean by splitting up of the times.

I'm taking the checking range (eg. 9:00 to 10:00, or 10:00 to 11:00, etc..) and the event range (eg. 9:15 to 11:30)
The goal is to determine how much event time crosses the checking time range.
An assumption I've made is that the event will never be equal to or greater than 24 hours.

There are certainly optimisations to be made as the formula is implemented. I don't know how I could simplify the approach though.

On my scrap piece of paper, I drew two gantt chart looking lines. The top line for the event range, the bottom line for the checking range.
I'll try to draw it in ascii. You may need to switch to courier font (or a fixed-width font). These examples are for items 1, 3, 5 and 6 in the list I posted before.


1. =0
eS eE
|-------|
|---------------|
cS cE

3. =eE-cS
eS eE
|-------|
|---------------|
cS cE

5. =cE-cS
eS eE
|-----------------------|
|---------------|
cS cE

6. =0
eE eS
------| |------
|---------------|
cS cE


"Norman Harker" wrote in message ...
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