+ Reply to Thread
Results 1 to 6 of 6

CA Alt Work Schedule/Day-Night shifts Timesheet

  1. #1
    Registered User
    Join Date
    07-01-2016
    Location
    California
    MS-Off Ver
    2010
    Posts
    8

    Question 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

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,805

    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.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    07-01-2016
    Location
    California
    MS-Off Ver
    2010
    Posts
    8

    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. #4
    Registered User
    Join Date
    07-01-2016
    Location
    California
    MS-Off Ver
    2010
    Posts
    8

    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
    Attached Files Attached Files

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,805

    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. #6
    Registered User
    Join Date
    07-01-2016
    Location
    California
    MS-Off Ver
    2010
    Posts
    8

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Work Schedule - 24/7 - 4 shifts per day, 6 people
    By as519999 in forum Excel General
    Replies: 3
    Last Post: 04-06-2015, 07:33 PM
  2. Replies: 0
    Last Post: 09-23-2014, 09:30 AM
  3. [SOLVED] Lorry driver shifts (day,night,saturday, sunday) variable payrate
    By aportik in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-17-2013, 06:25 AM
  4. Weekly average output for last entry on every Sunday night shifts
    By dinker454 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 09-18-2013, 11:51 AM
  5. Calculate AM and PM hourly shifts by morning or night
    By milestones5213 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-01-2013, 07:11 PM
  6. Work Schedule, 10hr Shifts, 4 on 3 off, Rotating Weekends Off
    By onflight1978 in forum Excel General
    Replies: 5
    Last Post: 12-21-2012, 03:38 PM
  7. Work Schedule Timesheet
    By Qin in forum Excel General
    Replies: 10
    Last Post: 03-15-2010, 08:19 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1