+ Reply to Thread
Results 1 to 5 of 5

Caculating # of Day Shift Hrs & Night Shift Hrs of a Given Shift

  1. #1
    Registered User
    Join Date
    07-29-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2003
    Posts
    6

    Caculating # of Day Shift Hrs & Night Shift Hrs of a Given Shift

    Hi Guys, I post this as I couldn't find a specific solution to my issue.
    I need to find a formula that can calculate the number of day shift and night shift hours in any given shift worked.
    The various formulas I have come across and used do not work for all scenarios.

    Day Shift Hours are: 06:00 to 18:00
    Night Shift Hrs are: 18:00 to 06:00

    Start Finish Day Hrs Night Hrs
    13:30 19:30 4.5 1.5


    The formulas should work for all possibilities and combination of hours such as:
    08:00-13:00 (5 day - 0 night)
    02:30-19:30 (12 day - 5 night)
    23:30-08:00 (2 day - 6.5 night)
    03:30-05:00 (0 day - 1.5 night)
    16:00-07:00 (3 day - 12 night)
    etc.

    The issue is there are night shift hours BEFORE the start of the day shift, and there are night hours AFTER the day shifts and vice versa.
    So someone who worked for instance 05:00 - 19:00 ... has 1 hour night shift before and after the 12hrs he does in the day ... how do I add those 1 hrs to get a sum of 2.
    Not to mention the nightmares of when the start time is LARGER than the Finish Time ... such as 23:00-00:00

    Any help would be greatly appreciated.

    Kindest of Regards,
    AK

  2. #2
    Forum Contributor
    Join Date
    01-07-2013
    Location
    south africa
    MS-Off Ver
    Excel 2003-13
    Posts
    210

    Re: Caculating # of Day Shift Hrs & Night Shift Hrs of a Given Shift

    Hello AK
    The way I see it, is that there are 12 different scenarios. Without VBA, this means fairly ugly nested IF statements.
    I will put something together that hopefully splits up the If statements to make them more readable.

    Regards
    Most helpful to mark solved items as such (see help for directions). Star ratings are always welcome.

  3. #3
    Forum Contributor
    Join Date
    01-07-2013
    Location
    south africa
    MS-Off Ver
    Excel 2003-13
    Posts
    210

    Re: Caculating # of Day Shift Hrs & Night Shift Hrs of a Given Shift

    formula to calc day_night hours.xlsx
    Hello AK
    I have attached a solution, the IF statements are still fairly horrible and suspect they could be modified further, particularly the night calculation. Perhaps you want to see to that.

    Regards

  4. #4
    Registered User
    Join Date
    07-29-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Caculating # of Day Shift Hrs & Night Shift Hrs of a Given Shift

    Thank you.

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Caculating # of Day Shift Hrs & Night Shift Hrs of a Given Shift

    Try this...

    Data Range
    A
    B
    C
    D
    E
    1
    In
    Out
    Total
    Day Shift
    Night Shift
    2
    8:00 AM
    6:00 PM
    10:00
    10:00
    0:00
    3
    2:30 AM
    7:30 PM
    17:00
    12:00
    5:00
    4
    11:30 PM
    8:00 AM
    8:30
    2:00
    6:30
    5
    3:30 AM
    5:00 AM
    1:30
    0:00
    1:30
    6
    12:00 AM
    6:01 AM
    6:01
    0:01
    6:00


    This formula entered in D2:

    =((A2>B2)*12+MEDIAN(B2*24,6,18)-MEDIAN(A2*24,6,18))/24

    Format as h:mm

    This formula entered in E2:

    =MOD(B2-A2,1)-D2

    Format as h:mm

    Select D2:E2 and copy down as needed.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ 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. Need Help on Time Sheet Formula (Day Shift OK/Night Shift=Problem)
    By jomapac in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-18-2013, 02:26 AM
  2. [SOLVED] Spliting Day Shift and Night Shift Hours
    By Goldbadger2 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-27-2013, 01:16 AM
  3. Replies: 4
    Last Post: 12-06-2012, 08:29 PM
  4. Replies: 4
    Last Post: 09-21-2012, 12:58 AM
  5. Day shift/night shift time calculation issue
    By STATEXCEL in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-31-2007, 08:48 AM

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