+ Reply to Thread
Results 1 to 5 of 5

Calculate night shift hours worked - working hours span the night shift start and end

  1. #1
    Registered User
    Join Date
    12-08-2011
    Location
    Pretoria, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    1

    Calculate night shift hours worked - working hours span the night shift start and end

    I'd like to calculate the night shift hours worked. - have been trying to for days now!

    Some workers have work hours that span both the Night -Starting time and -End times, some span accross only one of the night shift start/end times

    I have night shift hours: B6 = Starting time (06:00 PM) and B7 = End time (06:00 AM) (hh:mm AM/PM - no dates)
    I have working hours: C15 = Start (06:00) and D15 = End (22:00) (yyyy/mm/dd hh:mm)

    I've tried:

    =IF(OR((MEDIAN(0,D15-C15,D15-(A15+$B$7))>0),(MEDIAN(0,D15-C15,(A15+$B$7)-C15)>0)),(MEDIAN(0,D15-C15,(A15+$B$7)-C15)+MEDIAN(0,D15-C15,D15-(A15+$B$6))),"")
    ... which works great = 4:00

    BUT if I had C15 = Start (2011/11/25 03:00:00 PM) and D15 = End (2011/11/26 09:00:00 AM) - hypothetically :-)
    with the same formula = 15
    ... it should be 12:00

    I'd like to carry on with life... please

  2. #2
    Forum Contributor
    Join Date
    09-10-2011
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    176

    Re: Calculate night shift hours worked - working hours span the night shift start and

    Hi Gratch,

    Welcome to the forum!! Try the below formula.

    A2: 2011/11/25 03:00:00 PM
    B2: 2011/11/26 09:00:00 AM
    C2: Copy the below formula
    U2: 18:00
    T2: 6:00

    Please Login or Register  to view this content.
    This is provided by daddylonglegs...

    Regards,
    Humac
    Last edited by humacdeep; 12-11-2011 at 03:46 PM.

  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Calculate night shift hours worked - working hours span the night shift start and

    Try this version for night hours

    =D15-C15-(DAY(C15)<>DAY(D15))*(B$6-B$7)-MEDIAN(MOD(D15,1),B$6,B$7)+MEDIAN(MOD(C15,1),B$6,B$7)
    Audere est facere

  4. #4
    Registered User
    Join Date
    08-14-2012
    Location
    Cavite, Philippines
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Calculate night shift hours worked - working hours span the night shift start and end

    Hi there....

    I would like to ask for help for computing night differential of our employees,

    below are the example of our timesheet

    dtr.jpg

  5. #5
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,406

    Re: Calculate night shift hours worked - working hours span the night shift start and end

    mjane.tulin,
    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

    Do not post pictures of workbooks.
    Ben Van Johnson

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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