Figuring Hours of Night Differential Worked

Thanks:  0
Likes:  0

# Thread: Figuring Hours of Night Differential Worked

1. ## 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. ## 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. ## 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. ## 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. ## Re: Figuring Hours of Night Differential Worked

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

Best regards,

Charlie

## User Tag List

#### Posting Permissions

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