Excel Formula to Calculate Time Span Between Different Time Range.

Hi;

I am trying to calculate hours worked if time span fall between different time range.

			Range 1	Range 2	Range 3
Start Time	Finish Time	Total Hours	>14:00 and <16:00	>16:00 and <22:00	>22:00
08:00	14:00	6	0	0	0
08:00	15:00	7	1	0	0
08:00	18:00	10	2	2	0
10:00	17:00	7	2	1	0
10:00	23:00	13	2	6	1
12:00	02:00	14	2	6	4

I can get the total hours worked by using the MOD Function = MOD(B2-A2,1)*24 but struggling on how to calculate number of hours if finish time falls between different time range.

Any help will be much appreciated.

Thanks
Omer.

Thanks Daddylonglegs, much appreaciates. It works great.

The only trouble i am having now is if it overlaps to next day, finding it difficult to get it to work.

Following is what i am trying to achieve;

			Range 4	Range 5
Start Time	Finish Time	Total Hours	>23:00 and < 01:00	>01:00
12:00	04:00	16	2	3

Any help on this will be much appreciated.

Thanks.

Thanks Popi, much appreciated. Your solution is great and It could do the job but I will need to create a helper column for it.

Currently Data Entry are done on the Start and Finish Time so was hoping to find a formulae based solution rather than using a helper columns.

Thanks.
Omer.