J

#### Jan Kronsell

good enough.

I use the following formularto calculate how much time between start time

and end time lies between 5PM and 6AM.

=(B1<=A1)*(1-(C2)+(C1))+MIN((C1),B1)-MIN((C1),A1)+MAX((C2),B1)-MAX((C2),A1)

A1 is start time, B1 is End time, C1 is Early time limit (6 AM) and C2 is

late time limit (5 PM).

The formula does the job if i just enter fx 03:00 AM in A1 and 7:00 PM

Here is my challenge: I need to add the date to the Entry, so that I enter

09-01-08 03:00 PM and 09-01-08 7:00 PM.

I have tried a lot of different solutions, and also read the articles on

Chip Pearsons site. I can get i to work in some situations, but not in all.

For instance

09-01-08 03:00 AM and 09-03-08 7:00 PM will miscalculate.

I need a formula, that works on all date/time combinations.

This formula calculates how much time lies between 6 PM and 17 PM

=IF(INT(B2-A2)>0,((INT(B2-A2)*C1)+C2-MAX(C1,A2-INT(A2))+MAX(B2-INT(B2),C1)-C1)-IF(B2-INT(B2)>=A2-INT(A2),C1),MIN(C2,B2-INT(B2))-MAX(A2-INT(A2),C1))

But I simply do not seem to be able to change it to do the other

calculation. The correct answer to the above date/times should total to 30

hours.

From 09-01-08 03:00 AM to 09-01-08 06:00 AM = 2 hours

From 09-01-08 05:00 PM to 09-02-08 06:00 AM = 13 hours

From 09-02-08 05:00 PM to 09-03-08 06:00 AM = 13 hours

From 09-02-08 05:00 PM to 09-03-08 07:00 PM = 2 hours

Is there any way to achieve what I need?

Jan