Figuring Hours of Night Differential Worked

coliervile

Well-known Member
Joined
May 19, 2006
Messages
724
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top