+ Reply to Thread
Results 1 to 5 of 5

Computing for number of hours under Night Differential

  1. #1
    Registered User
    Join Date
    10-31-2008
    Location
    Philippines
    Posts
    47

    Computing for number of hours under Night Differential

    Hi,

    How do i compute for the number of hours that fall into the Night Differential hours within an employees shift? In one cell is the shift start time of an employee and on the other cell is the shift end time. There is no constant number of hours that an employee should complete. Night Differential time starts at 10:00 PM and ends at 6:00AM.

    Thanks
    Attached Files Attached Files
    Last edited by NBVC; 02-10-2011 at 05:55 PM.

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

    Re: Computing for number of hours under Night Differential

    Try this formula in C5

    =(C3-C2)*24-MEDIAN(22,6,MOD(C3,1)*24)+MEDIAN(22,6,MOD(C2,1)*24)-(INT(C3)-INT(C2))*(22-6)

    format cell as number
    Audere est facere

  3. #3
    Registered User
    Join Date
    10-31-2008
    Location
    Philippines
    Posts
    47

    Re: Computing for number of hours under Night Differential

    This Worked out fine! Thanks Daddy!!! Can I call you daddy?!?

  4. #4
    Registered User
    Join Date
    02-24-2011
    Location
    Williston, Vermont
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Computing for number of hours under Night Differential

    Quote Originally Posted by daddylonglegs View Post
    Try this formula in C5

    =(C3-C2)*24-MEDIAN(22,6,MOD(C3,1)*24)+MEDIAN(22,6,MOD(C2,1)*24)-(INT(C3)-INT(C2))*(22-6)

    format cell as number
    Wow! How this formula formulated?

  5. #5
    Registered User
    Join Date
    09-24-2015
    Location
    Los Angeles, CA
    MS-Off Ver
    2016
    Posts
    1

    Re: Computing for number of hours under Night Differential

    Hi Ken, I know this was a while ago but I have a similar issue. I am able to get it to work for shifts that start before 6am and end during regular time.

    I can't get it to work when a worker starts during daytime and go over into the night time premium.

    Also here is what is slightly different: Our nighttime premium are any hours worked between Midnight to 7AM.

    So we need to calculate the following ways...

    1. Worker who starts at 6am and works to 6pm. (1hr at the beginning of the shift of nighttime premium)

    2. the other way where the worker starts at 11pm and get off at 4am. (4hrs of nighttime premium at the end of the shift)

    There is a third possible situation (and can happen) where a worker would start at 6am and work to 3am the next morning. (1 hr at beginning of shift + 3 hours at the end of the shift)

    So I need to figure out how to make it work all three ways. Is it possible?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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