Hello,

I am trying to create a spreadsheet that will be used to show the expected production for an area based on the time of the day.

I need a little help brainstorming a way to pause the "production clock" when the employees are on scheduled breaks. I am able to calculate the elapsed time between the shift start and Now() but I am unable to find a good way to subtract out the times for the different breaks.

Andon System.xlsm

Examples of the different break times dependant on shift length.

8 Hr Day example
Units per hour expected 2
7:45 minutes of production time available

Break Schedule
6:00 AM Work start
9:30 AM Break Start
9:45 AM Break End
12:00 Lunch Break
12:00 Lunch End
2:30 End of Day

Expected units produced at 9:30 AM = 7
(The "production clock" would pause between 9:30 and 9:45 because the employees are on break)
Expected units produced at 9:45 AM = 7
(The "production clock" would un-pause at 9:45 and run until lunch Break)
Expected units produced at 12:00 PM = 11.5

10 Hr Day example
Units per hour expected 2
9:30 minutes of production time available

6:00 AM Work start
9:30 AM Break Start
9:45 AM Break End
12:00 Lunch Break
12:00 Lunch End
2:30 PM Break Start
2:45 PM Break End
4:30 End of Day