Computing for number of hours under Night Differential

**emanon132501** · 02-09-2011, 03:24 PM

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

**daddylonglegs** · 02-09-2011, 04:41 PM

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

**emanon132501** · 02-10-2011, 10:30 AM

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

**kenbarls** · 02-24-2011, 10:31 PM

Originally Posted by daddylonglegs

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?

**CharlesTrotter** · 09-24-2015, 02:13 AM

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?