Spreadsheet: 672651d1586950079-overtime-formula-shift-hours
Sheet: Shift Hours

 | Examples of Shift Hour calculations
 | [NAME] | =IF(End Time>Start Time Cells,IF(MOD(End Time+1,1)<=1/3,0,MIN(End Time Serial#,End Time)-IF(End Time<=Start Time Serial #,Start Time,MAX(Start Time,Start Time Serial #))), |  |  |  |  |  |  |  |  |  |  | Examples
 | Premise | MAX(End Time Serial#-MAX(Start Time Serial #,Start Time))+MAX(0,End Time-Start Time Serial #))*24 |  |  |  |  |  |  |  |  |  |  | Below
 | Shift Start Times |  |  | Shift End Times (start + 8 hours) |  |  |  |  | Shift Examples
 | Start/End | h:mmm:ss | Serial Number | h:mmm:ss | Serial Number | Fraction |  |  | Daily 3 x 8 Hour Shifts 6am - 2pm, Aft 2pm - 10pm, 10pm - 6am
 | 01:00:00 |  |  | 09:00:00 |  |  |  |  | Start Time | End Time | Day | Afternoon | Nights | Total Hrs
 | 02:00:00 |  | 0.08333333333333333 | 10:00:00 |  | 0.08333333333333333 |  |  | 06:00:00 | 14:00:00
 | 03:00:00 |  |  | 11:00:00 |  |  |  |  | 14:00:00 | 22:00:00
 | 04:00:00 |  |  | 12:00:00 |  |  |  |  | 22:00:00 | 06:00:00
 | 05:00:00 |  |  | 13:00:00
 | 06:00:00 |  |  | 14:00:00 |  |  |  |  | Daily 3 x 8 Hour Shifts 6am - 2pm, Aft 2pm - 10pm, 10pm - 6am. With Overtime.
 | 07:00:00 |  |  | 15:00:00 |  |  |  |  | Start Time | End Time | Day | Afternoon | Nights | Total Hrs
 | 08:00:00 |  |  | 16:00:00 |  |  |  |  | 06:00:00 | 22:00:00
 | 09:00:00 |  |  | 17:00:00 |  |  |  |  | 13:00:00 | 02:00:00
 | 10:00:00 |  |  | 18:00:00 |  |  |  |  | 22:00:00 | 08:00:00
 | 11:00:00 |  |  | 19:00:00
 | 12:00:00 |  |  | 20:00:00 |  |  |  |  | Daily 3 x 8 Hour Shifts 7am - 3pm, Aft 3pm - 11pm, 11pm - 7am
 | 13:00:00 |  |  | 21:00:00 |  |  |  |  | Start Time | End Time | Day | Afternoon | Nights | Total Hrs
 | 14:00:00 |  |  | 22:00:00 |  |  |  |  | 07:00:00 | 15:00:00
 | 15:00:00 |  |  | 23:00:00 |  |  |  |  | 15:00:00 | 23:00:00
 | 16:00:00 |  |  | 00:00:00 |  |  |  |  | 23:00:00 | 07:00:00 |  |  |  |  |  | Calculating Dates
 | 17:00:00 |  |  | 01:00:00 |  |  |  |  |  |  |  |  |  |  |  | Jan 1st
 | 18:00:00 |  |  | 02:00:00 |  |  |  |  | Daily 3 x 8 Hour Shifts 7am - 3pm, Aft 3pm - 11pm, 11pm - 7am. With Overtime. |  |  |  |  |  |  | Fiscal Year
 | 19:00:00 |  |  | 03:00:00 |  |  |  |  | Start Time | End Time | Day | Afternoon | Nights | Total Hrs |  | Today
 | 20:00:00 |  |  | 04:00:00 |  |  |  |  | 05:00:00 | 18:00:00 |  |  |  |  |  | Year & Month
 | 21:00:00 |  |  | 05:00:00 |  |  |  |  | 15:00:00 | 03:00:00 |  |  |  |  |  | Weekday>
 | 22:00:00 |  |  | 06:00:00 |  |  |  |  | 07:00:00 | 06:59:00 |  |  |  |  |  | Week Number |  | Current Year
 | 23:00:00 |  |  | 07:00:00 |  |  |  |  |  |  |  |  |  |  |  | 1st of Month
 | 09:00:00 |  |  | 17:00:00 |  |  |  |  |  |  |  |  |  |  |  | 1st Sunday
 | For different Start & End times use Find/Replace to change the relevant divisors or |  |  |  |  |  |  | Shift | [NAME] | Shift |  | Pay | Pay | [NAME] |  | 1st Monday
 | serial numbers to reflect the new timings. |  |  |  |  |  |  | Code | List | Premium |  | Scale | Grade | List |  | Last work Day>
 | eg: a 6am start is 1/4 and a 7am start would be 7/24 and a 2pm |  |  |  |  |  |  | 1 | Days | 0 |  | A | 1 | 8.75
 | end time would change from 7/12 to 5/8 for a 3pm finish. |  |  |  |  |  |  | 2 | Day Shift | 0.1 |  | B | 2 | 9.21
 | Change each shift at a time, replace all day formulas then change all afternoon formulas. |  |  |  |  |  |  | 3 | Aft Shift | 0.2 |  | C | 3 | 10.33
 | Note: [NAME] do not work for a straight 24hr shifts ie: 6am-6am or 7am-7am. {Use a helper} |  |  |  |  |  |  | 4 | Night Shift | 0.3 |  | D | 4 | 12.22
 | Regular Work Day - No Shifts |  |  |  |  | Starting an annual employee [NAME].
 | Start Time | End Time | Hours |  |  |  |  |  |  |  |  |  |  |  |  |  | Fill all 5 rows right to year end.
 | 09:00:00 | 17:00:00 |  |  |  | Start Date> | 2020-03-29 00:00:00 |  |  |  |  |  |  |  |  |  | In a 52 week schedule allow 1 day for leap years.
 | 06:00:00 | 14:00:00 |  |  |  | Today |  |  |  |  |  |  |  |  |  |  | Use CF formatting for highlighting weekends
 | 07:00:00 | 23:00:00 |  |  |  | Start Week # | 1 |  |  |  |  |  |  |  |  |  | Can be used with a Stat Holiday Calendar with CF
 |  |  |  |  |  | Employee | Name rows |  | Shifts > |  |  |  |  |  |  |  | formatting to highlight days off.
 | 06:00:00 |  | 14:00:00
 | Monday to Friday. Daily 3 x 8 Hour Shifts 6am - 2pm, Aft 2pm - 10pm, 10pm - 6am. With Pay Scale & Shift Premiums. |  |  |  |  |  |  | Total work hours & lunch breaks |  |  |  | Shift Premiums |  |  | [NAME] | Hourly | Total | Total | Total | Total
 | Employee | Pay | Start | End | Day | Afternoon | Night | [NAME] | Total | Regular Hrs | OT/Hrs | 0.1 | 0.2 | 0.3 | Premium | Pay | Regular | Overtime | Overtime | ALL
 | Name | Scale | Time | Time | Shift | Shift | Shift | 0.5 | Hours | Less Break | Less Break | Days | Aft | Nights | Totals | Rate | Paid Hours | 1.5 | 2 | Paid Hours
 | Hazle Nutt | A | 06:00:00 | 16:00:00
 | [NAME] | C | 23:00:00 | 08:00:00
 | [NAME] | D | 06:00:00 | 22:00:00
 | [NAME] | B | 14:00:00 | 02:00:00
 | Monday to Friday Daily 3 x 8 Hour Shifts 7am - 3pm, Aft 3pm - 11pm, 11pm - 7am. With Pay Scale & Shift Premiums. |  |  |  |  |  |  | Total work hours & lunch breaks |  |  |  | Shift Premiums |  |  | [NAME] | Hourly | Total | Total | Total | Total
 | Employee | Pay | Start | End | Day | Afternoon | Night | [NAME] | Total | Regular Hrs | OT/Hrs | 0.1 | 0.2 | 0.3 | Premium | Pay | Regular | Overtime | Overtime | ALL
 | Name | Scale | Time | Time | Shift | Shift | Shift | 0.5 | Hours | Less Break | Less Break | Days | Aft | Nights | Totals | Rate | Paid Hours | 1.5 | 2 | Paid Hours
 | [NAME] | A | 07:00:00 | 15:00:00
 | [NAME] | C | 15:00:00 | 03:00:00
 | Olive Branch | D | 07:00:00 | 23:00:00
 | [NAME] | B | 06:00:00 | 22:00:00
 | 09:00:00 | 17:00:00 | 06:00:00 | 14:00:00 | 14:00:00 | 22:00:00 | 23:00:00 | 07:00:00
 | Monday to Friday. Daily 3 x 8 Hour Shifts 6am - 2pm, Aft 2pm - 10pm, 10pm - 6am. With Pay Scale & Shift Premiums. |  |  |  |  |  |  |  | Total work hours & lunch breaks |  |  |  | Shift Premiums |  |  | [NAME] | Hourly | Total | Total | Total | Total
 | Employee | Pay | Shift | Start | End | Day | Afternoon | Night | [NAME] | Total | Regular Hrs | OT/Hrs | Using Shift Codes |  |  | Premium | Pay | Regular | Overtime | Overtime | ALL
 | Name | Scale | Code | Time | Time | Shift | Shift | Shift | 0.5 | Hours | Less Break | Less Break | Days | Aft | Nights | Totals | Rate | Paid Hours | 1.5 | 2 | Paid Hours
 | [NAME] | A |  | 09:00:00 | 17:00:00
 | [NAME] | B |  | 06:00:00 | 02:00:00
 | [NAME] | C |  | 14:00:00 | 02:00:00
 | [NAME] | D |  | 22:00:00 | 06:00:00
 | 12 Hour Shifts | Start | End | Day | Night | Total
 | 6am - 6pm | Time | Time | Shift | Shift | Hours
 | Day | Clock in | Clock out | Day Hours | Night Hours | Worked
 | Monday | 06:00:00 | 18:00:00
 | Tuesday | 17:00:00 | 05:00:00
 | Wednesday | 17:30:00 | 05:00:00
 | Thursday | 06:00:00 | 17:00:00
 | Friday | 23:00:00 | 08:00:00
 | Saturday | 15:00:00 | 23:00:00
 | Sunday | 21:00:00 | 04:00:00
 | 06:00:00 | 0.25 | 18:00:00 | 0.75
 | 18:00:00 | 0.75 | 06:00:00 | 0.25
 | Weekly Hours unpaid lunch. |  |  |  |  |  |  |  |  | Weekly Hours unpaid lunch with overtime MAX 44 weekly hours.
 | Week of: |  | Name: | [NAME] | Sophia | [NAME] | Hailey |  |  | Name : | [NAME] |  |  | Hours | Pay
 | 2020-03-28 00:00:00 |  | [NAME] Pay: | 12 | 14 | 11 | 14 |  |  | [NAME]: | 11 |  | Regular Hours
 |  |  |  |  |  |  |  |  |  | Overtime Rate: | 1.5 |  | Overtime Hours
 |  |  |  |  |  |  |  |  |  | Overtime Wages: |  |  | TOTAL
 | DANIEL | Log in | [NAME] | Lunch Ends | Log Out | Hrs worked | Total Pay
 | Monday | 09:00:00 | 11:40:00 | 12:10:00 | 19:00:00 |  |  |  |  | Date | Log in | [NAME] | Lunch Ends | Log Out | Hrs worked | Regular Hrs
 | Tuesday | 09:00:00 | 11:40:00 | 12:10:00 | 18:00:00 |  |  |  |  | Monday | 09:00:00 | 13:00:00 | 13:30:00 | 18:00:00
 | Wednesday | 09:00:00 | 11:40:00 | 12:10:00 | 19:00:00 |  |  |  |  | Tuesday | 09:20:00 | 11:57:00 | 12:15:00 | 18:00:00
 | Thursday |  |  |  |  |  |  |  |  | Wednesday | 09:00:00 | 12:00:00 | 12:32:00 | 19:15:00
 | Friday |  |  |  |  |  |  |  |  | Thursday | 09:00:00 | 11:40:00 | 12:15:00 | 21:00:00
 | Saturday |  |  |  |  |  |  |  |  | Friday | 09:20:00 | 11:57:00 | 12:15:00 | 20:00:00
 | Sunday |  |  |  |  |  |  |  |  | Saturday
 |  |  |  |  | TOTAL |  |  |  |  | Sunday
 | Weekly Hours unpaid lunch with overtime MAX 44 weekly hours.
 | Month: |  |  | [NAME] | 16 | Overtime starts after
 | Name: | [NAME] |  | Overtime rate | 1.5 | 44 | hours
 |  | Mon | [NAME] | Wed | Thurs | Fri | Sat | Sun
 | Log In | 09:00:00 | 09:15:00 | 08:50:00 | 09:20:00 | 11:10:00 | 12:15:00
 | [NAME] | 12:00:00 | 12:00:00 | 12:10:00 | 12:00:00 | 12:15:00
 | Lunch Ends | 12:30:00 | 12:15:00 | 12:30:00 | 12:30:00 | 12:30:00
 | Log Out | 21:00:00 | 19:00:00 | 23:00:00 | 17:30:00 | 17:00:00 | 16:00:00
 | Hours Worked
 |  |  | Reg. Hrs. |  | Over. Hrs. |  | Total Hrs.
 |  |  | [NAME]. Pay |  | Over. Pay: |  | Total Pay
 |  | Start Time | End Time |  | Elapsed Time
 |  | 07:45:00 | 10:30:00
 |  | Same Day
 |  | Start Time | End Time |  | Elapsed Time
 |  | 07:45:00 | 22:30:00
 |  | Over Night
 |  | Start Time Day 1 | End Time |  | Elapsed Time
 |  | 18:00:00 | 08:00:00
 |  |  |  |  |  |  |  | [NAME] |  |  |  | Rate
 | Hours worked with timed lunch breaks |  |  |  |  |  |  | 8 |  |  |  | 1.5
 | Name | Start | Lunch Out | Lunch In | Time End | Totals Hrs | Paid Hours | [NAME] | [NAME] | $/Hr | Reg $ | OT/$ | Total
 | [NAME] | 09:00:00 | 12:00:00 | 12:30:00 | 18:00:00 |  |  |  |  | 20
 | [NAME] | 08:30:00 | 11:30:00 | 12:30:00 | 21:00:00 |  |  |  |  | 18
 | [NAME] | 22:00:00 | 11:30:00 | 12:30:00 | 06:00:00 |  |  |  |  | 18
 | Hours worked with 0.5hr lunch breaks deducted
 | Name | Start | Time End | Totals Hrs | Paid Hours | [NAME] | $/Hr | Reg $ | OT/$ | Total
 | [NAME] | 09:00:00 | 18:00:00 |  |  |  | 20
 | [NAME] | 08:30:00 | 20:30:00 |  |  |  | 18
 | [NAME] | 22:00:00 | 12:00:00 |  |  |  | 22.5