Building an Employee Shift Schedule that Automatically Deducts Breaks

**derdoktor** · 06-09-2016, 07:13 PM

I'm trying to create a simple employee shift schedule that automatically removes half-hour breaks. So far, I've muddled through the formula to add up hours worked by day, however, I cannot seem to figure out how to prevent a break from a day without hours worked. In this screenshot example, I have an employee with five 8.5 hour shifts with formula to deduct 0.5 hours from each day. Unfortunately, on the two days off, the breaks are deducted. Ideally, the formula for the 40-hour work week should return a result of 40. As you can see, it reads 39. The formula cell is R146.

Please help. Also, I'm not sure how unnecessarily complex this formula is. If there is a simpler way to do this, I'd greatly appreciate the input.

Thanks,

Anthony
RVA

Excel Screenshot.png
Excel Screenshot.png

**derdoktor** · 06-09-2016, 07:23 PM

Apparently, I cannot get the screenshot to attach.

This formula (for R146) gathers data from a row of 14 horizontal cells (D146:Q146). Each set of 2 cells represents a shift start time and end time for a day.

Here is the formula I've concocted...

=SUM(((E146-D146)*24)-0.5)+(((G146-F146)*24)-0.5)+(((I146-H146)*24)-0.5)+(((K146-J146)*24)-0.5)+(((M146-L146)*24)-0.5)+(((O146-N146)*24)-0.5)+(((Q146-P146)*24)-0.5)

Thanks again!

**windy58** · 06-10-2016, 04:16 AM

Try this.....

Not very pretty but.....

=IF(D146="",0,((E146-D146)*24)-0.5)+IF(F146="",0,((G146-F146)*24)-0.5)+IF(H146="",0,((I146-H146)*24)-0.5)+IF(J146="",0,((K146-J146)*24)-0.5)+IF(L146="",0,((M146-L146)*24)-0.5)+IF(N146="",0,((O146-N146)*24)-0.5)+IF(P146="",0,((Q146-P146)*24)-0.5)

Windy

**derdoktor** · 06-10-2016, 06:39 AM

THANK YOU Windy! You are a badass.

**windy58** · 06-11-2016, 04:09 AM

No Problem, glad to have been of assistance.

Windy

Building an Employee Shift Schedule that Automatically Deducts Breaks

LinkBack

Thread Tools

Rate This Thread

Display

Building an Employee Shift Schedule that Automatically Deducts Breaks

Re: Building an Employee Shift Schedule that Automatically Deducts Breaks

Re: Building an Employee Shift Schedule that Automatically Deducts Breaks

Re: Building an Employee Shift Schedule that Automatically Deducts Breaks

Re: Building an Employee Shift Schedule that Automatically Deducts Breaks

Thread Information

Users Browsing this Thread

Similar Threads

Add employee name to schedule based on Shift and date

Creating an automatic shift schedule within an all ready created shift schedule?

Suggestions for implementing a automatically shift schedule display page?

Employee Schedule - Subtracting a 30 min lunch from a schedule 6 or more hours long

Shift Schedule - 3 Shift Coverage - Auto Populate Roll-up Summary Schedule

calculate time start and finish times and deducts breaks

editing a formula that automatically deducts breaks

Bookmarks

Bookmarks

Posting Permissions