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