Results 1 to 5 of 5

Figuring Hours of Night Differential Worked

This is a discussion on Figuring Hours of Night Differential Worked within the Excel Questions forums, part of the Question Forums category; I need a formula to figure the number of hours that a person works in there 8 hour shift that ...

  1. #1
    Board Regular
    Join Date
    May 2006
    Location
    Oklahoma
    Posts
    586

    Default Figuring Hours of Night Differential Worked

    I need a formula to figure the number of hours that a person works in there 8 hour shift that falls under the category of Night Differential. An employee is paid an extra 10% per hour more if during their assigned shift is from 6:00p.m. (1800) until 6:00 a.m. (0600) the next day. For example if an employee is scheduled to work from 6:00 p.m. on Monday evening until 2:00 a.m. Tuesday the next morning the employee would have worked 8 hours of Night Differential as well as their regular 8 hour shift. How can I use a formula that will figure out the number of hours, of Night Differential, that an employee works if they work from 1445 until 2245 (answer should be 4.75 hours) and from 2300 to 0700 (the next morning. I also need the same thing to figure out the number of hours an employee works on Sunday if an employee starts their shift on Sunday at 1800 and ends their shift on Monday morning at 0200 (answer should be 6 hours).

    Cell....................B1 is SUN and so on across worksheet
    ....................B2 is 4/13

    A3 is Start Time....B3 is 1445

    A4 is End Time......B3 is 2245

    A5 is Night...........B5 is (number of hours)

    Best regards,

    Charlie

  2. #2
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,917

    Default Re: Figuring Hours of Night Differential Worked

    If start of shift is in A2 and end of shift in B2 (both in time format) and shift length is always 8 hours then this formula will give the "night hours" worked

    =IF(A2>11/12,7/12-B2,IF(A2<1/4,1/4-A2,MEDIAN(0,1/3,A2-5/12)))

    format as time

  3. #3
    Board Regular
    Join Date
    May 2006
    Location
    Oklahoma
    Posts
    586

    Default Re: Figuring Hours of Night Differential Worked

    Thanks "barry houdini" for your formula and it does work great. If I wanted the answer to be formated as Accounting with two decimals (e.g. 4.75 instead of 4:45) how would that affect the formula? I did try just reformatting the cell with the answer to Accounting, but it came out to 0.20 .

    Best regards,

    Charlie

  4. #4
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,917

    Default Re: Figuring Hours of Night Differential Worked

    Just multiply by 24, i.e.

    =IF(A2>11/12,7/12-B2,IF(A2<1/4,1/4-A2,MEDIAN(0,1/3,A2-5/12)))*24

    format as number

  5. #5
    Board Regular
    Join Date
    May 2006
    Location
    Oklahoma
    Posts
    586

    Default Re: Figuring Hours of Night Differential Worked

    Thanks you Sir it worked perfectly. Have a great day.

    Best regards,

    Charlie

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com