CA Alt Work Schedule/Day-Night shifts Timesheet

Hello,
I am looking to create timesheet and need some assistance with the formulas. The shifts are 12 hour shifts, and separated as day or night as payroll will pay a different rate for the night shift. Each day is recorded separately even if the shift goes from 7pm - 7am. Military time is used and the total displayed in [H]MM. Employees can waive one of their 2 meal breaks if desired. Regular Time (RT) then is a 12 hour shift, Double Time (DT) is time over 12 hours even if across two days (ie: 7pm - 8:30 am), and Over Time (OT) is over 40 hours in a week.

I've been happy with what I have so far (sorry, wasn't able to attach the spreadsheet).
Headers are:
Date Start Time 1 lunch out 1 lunch in 2nd lunch out 2nd lunch in End Time RT OT DT Day/Night

RT daily formula =IF(C5-B5+(E5-D5)+(G5-F5)>12/24,12/24,C5-B5+(E5-D5)+(G5-F5))
OT formula auto calculate from week RT Total =MAX(0,SUM(I5:I11)-40/24)
DT daily formula =IF(C5-B5+(E5-D5)+(G5-F5)>12/24,(C5-B5+(E5-D5)+(G5-F5)-12/24),0)

Date In Out In Out In Out RT OT DT Code
12/19/16 7:00 12:00 12:30 16:30 9:00 0:00 Day
12/20/16 8:00 13:15 13:45 19:30 11:00 0:00 Day
12/20/16 19:30 21:30 2:00 0:00 Night
12/21/16 6:00 12:00 12:30 19:30 12:00 1:00 Day
12/22/16 19:00 24:00 5:00 0:00 Night
12/23/16 0:00 0:30 1:00 8:45 8:15 0:00 Night
47:15 40:00 7:15 1:00
The 47:15 is a hidden calculation so that the 40 RT shows as the total.

With the above, I'm pretty happy with, but need assistance with the OT & DT when crosses over into the next day. Example above: 12/20/16, the second entry should be 1 hour RT and 1 hour DT. Also, 12/23/16 is a carry over from 12/22/16 so the OT total should be 5 and the DT total should be 2:15 in HH:MM. Any Ideas?

Thank you much,
Gina

Hi MarvinP,

Thank you for your interest and questions. The day/night column is also used to record PTO, bereavement leave, etc. It is not currently used for any calculations, but thought it might be useful if needed to do so. Day shift is 7:00 am - 7:30 pm and night shift 7:00 pm - 7:30 am.

I had tried entering the night shift as one day on 12/22 as: IN at 19:00 Out at 00:30 IN at 01:00 Out at 08:45, but the total hours for that day did not display (too long it said). Probably due to the meal breaks in and out and I think by CA law, need to give the option of the second meal break (If I'm following your suggestion correctly).

Every employee will have their own single spreadsheet that we would create for them. Our work week starts on Sunday at 12:00 am.

Hope that helps explain the logic of the set up.
Gina

Hi MarvinP,

Thank you for your interest and questions. The day/night column is also used to record PTO, bereavement leave, etc. It is not currently used for any calculations, but thought it might be useful if needed to do so. Day shift is 7:00 am - 7:30 pm and night shift 7:00 pm - 7:30 am.

I had tried entering the night shift as one day on 12/22 as: IN at 19:00 Out at 00:30 IN at 01:00 Out at 08:45, but the total hours for that day did not display (too long it said). Probably due to the meal breaks in and out and I think by CA law, need to give the option of the second meal break (If I'm following your suggestion correctly).

Every employee will have their own single spreadsheet that we would create for them. Our work week starts on Sunday at 12:00 am.

Hope that helps explain the logic of the set up.
I think I successfully attached the spreadsheet here.
Gina

Hi Marvin P,
Thank you kindly for the suggestions. I still had the same issues but was able to fix the negative time issue by incorporating (G5<B5) into the formula and making the entries as one shift/day.
Gina