Total Working Hours Minus Break + Lunch Times

**Rosco88** · 12-12-2013, 11:51 AM

Good Afternoon,

I am hoping somebody can assist me.... Slowly losing my mind with this one...

I need a formula that can deduct morning and lunch breaks based on time worked.

In the attached spreadsheet I have a Data Validation list in columns D + E, This shows the start and finish time on the machine and a calculation of the total run time in Column F.

The machine run times are:

Start: 08:00
Break: 10:15-10:30
Lunch: 13:00-13:30
Finish: 16:00

If a machine starts anywhere between 08:00-10:15 & finishes anywhere between 13:30-16:00 I need 45mins (break + lunch) deducting from total time.

If a machine starts anywhere between 08:00-10:15 & finishes anywhere between 10:30-13:00 I need 15mins (break) deducting from total time.

If a machine starts anywhere between 10:30-13:00 & finishes anywhere between 13:30-16:00 I need 30mins (lunch) deducting from total time.

If a machine runs anwhere inside of these times, for example 7:15-9:45 there shoudl be no deduction.

I hope this makes sense and i hope somebody can help.

Many Thanks In Advance
Ross

DAILY PROD SHEET - VMC.xlsm

**Melvinrobb** · 12-12-2013, 04:29 PM

What happens if the time is 8:00 - 10:20?
10:20 to 12:45?

**Rosco88** · 12-13-2013, 05:01 AM

Hi Melvinrobb,

In 'theory' the times entered on the spreadsheet should never be input between 10:15-10:30 & 13:00-13:30 as all machines are down between these times

Regards
Ross