Calculating Work hours overnight with a 6th hour meal period

Good Morning,
I have search the forum to no avail. I'm am trying to make a work schedule that will calculate a 30 meal period if a employee works a 6 hour shift. and also calculate time right when working a overnight shift. The current setup i have is
B5=10:30 pm (START SHIFT)
C5=6:30 am (END SHIFT)
=(C5-B5)*24-IF((C5-B5)*24>5,0.5,IF((C5-B5)*24>6,0.5,0)). This is the formula i have now. It does great with the 30 meal period after the 6th hour. but when i throw in a overnight it gives me a negative answer.

Thank you for your time
Ryan

Schedule WE 11-30-14.xlsx

Hi,

Try below formula:

=MOD(C5-B5,1)*24-IF(MOD(C5-B5,1)*24>5,0.5,IF(MOD(C5-B5,1)*24>6,0.5,0))

But I still don't understand using two condition in IF >5 & >6 with same result of 0.5 , I think it can be reduced to one as >5.

Regards,

Thank you Misrasmendra,
I dont have time right now to look in detail at the equation. But when i inserted your formula it worked. I got the formula i had before from a different forum. When i get home im gonna play with it some more. And make sure it takes a 30 minute meal period out at the 6th hour.
THIS IS AWESOME!!

Thank you again Misrasomendra

Thanks for the feedback and glad it worked for you.

Regards,