# CA Alt Work Schedule/Day-Night shifts Timesheet

1. ## 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).
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

2. ## Re: CA Alt Work Schedule/Day-Night shifts Timesheet

Hi Gina,

Do you need the Day/Night column? If a shift starts in the daytime (say between 5am and noon) it is a day shift and after that it becomes a night shift?

Might it be easier to NOT cross dates for a shift. That means you would enter two rows for a shift that went across midnight. It also means you could have a single In and Out columns instead of 3.

I'm looking at your problem as an Excel person, thinking duplicate In/Out columns is not good from an Excel perspective.

Will you have many workers on a single sheet or will you create a sheet for each persons?

Is the start of a week on a Sunday midnight?

I have a lot of open questions with your table structure and how you intend to produce what you need from your structure above.

3. ## Re: CA Alt Work Schedule/Day-Night shifts Timesheet

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

4. ## Re: CA Alt Work Schedule/Day-Night shifts Timesheet

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

5. ## Re: CA Alt Work Schedule/Day-Night shifts Timesheet

Hi Gina,

Regular hours might be easier with this formula.
=MIN(((G5-B5)-(D5-C5)-(F5-E5)),12/24)
And Double Time then looks like this:
=MAX(((G5-B5)-(D5-C5)-(F5-E5)-12/24),0)

DRAFT AWS Timesheet for Gina.xlsx

6. ## Re: CA Alt Work Schedule/Day-Night shifts Timesheet

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

There are currently 1 users browsing this thread. (0 members and 1 guests)