=End-Start-TIME(,30,)*AND(Start<TIME(12,,),End>TIME(12,30,))

--ron

=B2-A2-TIME(0,30,0)*AND(A2<=TIME(12,30,0),B2>=TIME(12,0,0))

Hi,

If you are looking for a general solution, as I see it there are 6
possibilities:

1. Start before 12, end after 12:30
2. Start before 12, end before 12
3. Start before 12, end during lunch (say 12:15)
4. Start after lunch, (end after lunch )
5. Start during lunch, end after lunch
6. Start during lunch, end during lunch

To cover all of these situations the formula is a little bit more
complicated, suppose your Start time is in cell A1 and your End time in B1
then:

=IF(AND(B1>=TIME(12,30,),A1<=TIME(12,,)),B1-A1-TIME(,30,),IF(AND(A1>=TIME(12,,),B1<=TIME(12,30,)),0,IF(AND(A1>TIME(12,,),A1<TIME(12,30,)),B1-TIME(12,30,),IF(AND(A1<=TIME(12,,),B1>TIME(12,,)),TIME(12,,)-A1,B1-A1))))

To simplify this formula:
In cell F1 enter =TIME(12,,)
In cell F2 enter =TIME(12,30,)
Name cell F1 L and name cell F2 U

Then you can use the revised version of the above formula:

=IF(AND(B1>=U,A1<=L),B1-A1-TIME(,30,),IF(AND(A1>=L,B1<=U),0,IF(AND(A1>L,A1<U),B1-U,IF(AND(A1<=L,B1>L),L-A1,B1-A1))))

To further simplify the formula you can determine if any of the 6 conditions
I listed would never apply and remove the appropriate portion of the formula.
For example suppose they can never start or end during lunch, then the
formula can be simplified to read:

=B1-A1-AND(A1<0.5,B1>12.5/24)*.5/24

In this case I have not used range names but I have eliminated the need for
the longer TIME() function.

or with the range names

=B1-A1-(AND(A1<L,B1>U)*.5/24)

How would you do this manually, on paper? When you determine that, you can
do it in Excel using the same formula.

Tyro

Thanks very much Ron