# Need formula to calculate number of hours in a time span that are between 6PM and 6AM

1. ## Need formula to calculate number of hours in a time span that are between 6PM and 6AM

Our employees work many different shifts. Whatever shift they work, they get night premium for those hours that fall between 6PM and 6AM the next morning. Some shifts start and end before midnight, and other shifts start before midnight and go until after midnight, and some start before midnight and go until after 6AM, and others start at midnight and go until after 6AM. Below is an attachment showing some example shifts. I need a formula that will calculate the number of hours in the shift that fall between 18:00 and 06:00. If possible, I need the same formula to be used whether or not the shift crosses midnight. Thanks in advance for any help.

Test.xlsx  Register To Reply

2. ## Re: Need formula to calculate number of hours in a time span that are between 6PM and 6AM ``Please Login or Register  to view this content.``  Register To Reply

3. ## Re: Need formula to calculate number of hours in a time span that are between 6PM and 6AM Originally Posted by ConneXionLost  ``Please Login or Register  to view this content.``
That gives me the total hours. I need it to give me the hours during that span that fall between 6PM and 6AM.  Register To Reply

4. ## Re: Need formula to calculate number of hours in a time span that are between 6PM and 6AM

Try this formula in C2 copied down

=MOD(B2-A2,1)*24-(B2<A2)*12-MEDIAN(B2*24,6,18)+MEDIAN(A2*24,6,18)

format as number  Register To Reply

5. ## Re: Need formula to calculate number of hours in a time span that are between 6PM and 6AM

Thanks daddylonglegs. That worked for me.  Register To Reply

6. ## Re: Need formula to calculate number of hours in a time span that are between 6PM and 6AM

=MOD(B2-A2,1)*24-(B2<A2)*12-MEDIAN(B2*24,6,18)+MEDIAN(A2*24,6,18)

How would this formula read if you wanted to calculate time span between midnight and 4AM?  Register To Reply