Calculating time, potential lunch break

**B1123** · 10-08-2008, 06:00 AM

Hi All

I'm struggling with a formula.

I am creating a worksheet where employees schedule work throughout the day
eg task 1 will be start time 9am, finish time 11am. The result is 2 hours to do the task.

The problem i'm having is that I need to do a calculation that if a task includes lunch then the result takes an hour away from the task time.
eg task 2 will be start time 12pm, finish time 3pm, excel displays 3 hours, i need it to say 2 hours because 1-2pm is contractual lunch.

I can't just do a day formula that says minus 1 from total as this is task orientated and will vary depending on start and finish times of each task.

I hope someone can help.
PS, it needs to be a formula and not vba as we have a problem with macro's if multiple people use the worksheet.

**Gary Brown** · 10-08-2008, 08:05 AM

The formual below assumes the start time is in cell A1 and the end time is in B1. It also assumes that if the start time or end time fall between 1 and 2, then 1 hour is deducted, I'm not sure this is true if the start time is 1:30pm?

Here is the formula:-

=IF(OR(AND(A1>=TIME(13,0,0),A1<=TIME(14,0,0)),AND(B1>=TIME(13,0,0),B1<=TIME(14,0,0))),(B1-A1)-TIME(1,0,0),IF(AND(A1<TIME(13,0,0),B1>TIME(14,0,0)),(B1-A1)-TIME(1,0,0),B1-A1))

Gary

**daddylonglegs** · 10-08-2008, 08:17 AM

Try

=IF(COUNT(A1,B1)=2,B1-A1-AND(A1<="13:00"+0,B1>="14:00"+0)/24,0)

format as time

**B1123** · 10-08-2008, 09:18 AM

Thank you both

Both formula's seem to work EXCEPT I have the same problem in both

If I put a task in from 1pm to 3pm your calculations are correct i get 1 hour yippee

However if I put in a task from 2pm to 3pm it is now coming up with 0 hours, this is incorrect as lunch is 1pm-2pm. I tried to play with both formula's to say 1:59:00 but this is not working.

Any advice would be hugely grateful

**daddylonglegs** · 10-08-2008, 06:38 PM

If you have 14:00 in A1 and 15:00 in B1 then I believe that the formula I suggested will correctly return 1:00......but here's a better formula

=IF(COUNT(A1,B1)=2,B1-A1-MAX(0,MIN(B1,"14:00")-MAX(A1,"13:00")),0)

It counts all hours between the two times except those between 13:00 and 14:00, even if start or end times are between those 2, e.g. 13:20 to 14:20 will give a result of 0:20.

Formula only works when A1 and B1 are on the same day, i.e. B1 is greater than A1.....

**B1123** · 10-09-2008, 04:14 AM

Hi Daddylonglegs

Still not working,

I have attached a sample of my doc for you to look at, D13-D16 are the actuals, reading 1,2,3,1 hours respectively.

I need to apply a formula in e13-16 excluding lunch between 1pm and 2pm so the results should be 1,2,2,1 respectively

I hope you can help

**Gary Brown** · 10-09-2008, 07:42 AM

Amend my formula as follows:-

=IF(OR(AND(A1>=TIME(13,0,0),A1<TIME(14,0,0)),AND(B1>=TIME(13,0,0),B1<=TIME(14,0,0))),(B1-A1)-TIME(1,0,0),IF(AND(A1<TIME(13,0,0),B1>TIME(14,0,0)),(B1-A1)-TIME(1,0,0),B1-A1))

Regards

Gary

**daddylonglegs** · 10-09-2008, 10:35 AM

You can adjust my formula to your specific cell references and multiply by 24 to get decimal hours, i.e. in D13 copied down

=IF(COUNT(B13,C13)=2,(C13-B13-MAX(0,MIN(C13,"14:00")-MAX(B13,"13:00")))*24,0)

format cell as number

Note: that your times all appear to be 1 second past the hour, I don't know if that's deliberate, so for row 15 my formula will give you the number of hours between the end of lunch, i.e. 14:00:00 and the end time in C15 16:00:01, i.e 2 hours and 1 second which converts to approx 2.000278