Calculate amount of time between two times and deduct 30-minutes

[mavsounds]

Hi,

I'm building a shift schedule for a restaurant.

I want to have one cell with start time, one cell with end time, and one cell with duration. However, I want the duration to automatically subtract a 30-minute unpaid meal-period. I found an old thread with the formula below. It works great. But, I want to add one condition that it duration returned value is the actual duration if the total duration is less than 5 1/2 hours. I can't figure that "if" statement. Any ideas?

=(M10-L10+IF(MOD(M10-L10,1)<0.5,(M10>L10),-(M10>L10)))-TIME(0,30,0)

 

[iliace]

=(M10-L10+IF(MOD(M10-L10,1)<0.5,(M10>L10),-(M10>L10)))-TIME(0,30,0)

I don't think I understand the purpose of the IF statement in this formula. The modulus division result of any decimal divided by 1 will be the decimal itself. For example, if L10=8:00 and M10=21:00, the result is a negative number (surely not what you want). Is it intended to cover overnight shifts? Can you post some examples of L10 and M10 values where this works correctly? Or, do you have a link to the original thread where you found this?

it duration returned value is the actual duration if the total duration is less than 5 1/2 hours. I can't figure that "if" statement. Any ideas?

Not sure what you mean by this, either (maybe I am having a slow moment - bear with me ). What is "actual duration" vs "total duration"? Do you mean that is the shift is shorter than 5 1/2 hours, you do not subtract the 30 minutes?

Would this work for you?

=(M10+(L10>M10)-L10) - TIME(0,30,0)*(((M10+(L10>M10))-L10)>TIME(5,30,0))