Calculate hours during set times for night shift allowance

Hi Everyone,


I'm trying to calculate the number of hours worked between two set times (20:00 - 08:00).

The shifts go overnight so I need a formula to know when that happens. Also, the start and finish time can be before 20:00 and finish after 08:00.

I have attached a simple excel sheet which has the correct hours manually entered. The cells highlighted green was the one I has having issues with the following formula (not my work).

=IF(B2<C2,MAX(MIN("24:00:00",C2)-MAX("20:00:00",B2),0)+MAX(MIN("8:00:00",C2)-MAX("0:00:00",B2),0),MAX(MIN("30:00:00",C2+1)-MAX("20:00:00",B2),0)+MAX(("8:00:00"-B2),0))*24

I'm hoping you can help me.


Thanks,
mbeire

Hi Leelnich,

That formula is exactly what I needed. Thanks for the extremely quick response.

I have marked the thread solved and provided rep to leelnich.

Thanks

Originally Posted by leelnich

Hi mbeire, welcome to the forum. Paste this in D2 and copy down:

Please Login or Register  to view this content.

Hi Leelnich,

That formula is exactly what I needed. Thanks for the extremely quick response.

I have marked the thread solved and provided rep to leelnich.

Thanks

Originally Posted by leelnich

Hi mbeire, welcome to the forum. Paste this in D2 and copy down:

Please Login or Register  to view this content.

Hi leelnich,

Thanks again for the formula. I notice the formula only return whole numbers. It will not count 1/4 or 1/2 hours as some shifts can be start different hours (15,30,45 mins). Can the current formula be adapted?