comparing time intervals

hello.

i need to compare time intervals to check, in hours, the amount of time
common to the intervals.

for example,

let's imagine i have these reference intervals:

1st reference: 23h00m - 07h00m (A1: "23:00" B1: "07:00")
2nd reference: 13h00m - 20h00m (A2: "13:00" B2: "20:00")

if the user input (G1:H1, for example) interval is 08h00 - 15h00 i need
it to return 2h (because the user input interval has 2h in common with
the second reference)

if the user input interval is 19h30 - 08h30 i need it to return 8h30
(8h from the first reference and 30m from the second)

if the user input intervals is 00h00 - 09h00 i need it to return 7h (7h
from the first reference interval)

i've searched the web but could not find a solution to suit my needs.
i think it may be possible to do it with tons of IF() functions and i
even started essaying it but maybe there is a simpler way to do it.

thanks.


--
znaya
------------------------------------------------------------------------
znaya's Profile: http://www.excelforum.com/member.php...o&userid=34242
View this thread: http://www.excelforum.com/showthread...hreadid=539941