Check time in cells to see if it goes across lunch

[koob]

Hi all

Is there a way to check the time in two cells to make sure that it doesn't count a lunch break. ie

Start time is 8:45 end time is 16:25 so I need to subtract 30 minutes, however if I run an if statement
and say use >12:30 if the job starts after 12:30 it will still subtract 30 minutes which I don't want.


Also If it ends before 12:00 again no time subtraction.

Thanks all

 

[Ron Rosenfeld]

Hi all

Is there a way to check the time in two cells to make sure that it doesn't count a lunch break. ie

Start time is 8:45 end time is 16:25 so I need to subtract 30 minutes, however if I run an if statement
and say use >12:30 if the job starts after 12:30 it will still subtract 30 minutes which I don't want.


Also If it ends before 12:00 again no time subtraction.

Thanks all

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


--ron

 

[David Biddulph]

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

 

[ShaneDevenshire]

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)

 

[Tyro]

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

Tyro