Formula to calculate lunch time greater than 30 minutes.

[DataFolks]

I would like for someone to assist me with a formula to calculate the amount of break time that is greater than 30 minutes in a day. I need to be able to enter this time in the format of hours: minutes same format as in/out punches.

Take, for example, the total lunch taken in cell F18 as 01:00:00
IF F18 is equal to "OOO" then G19 should be "0"
IF F18 is 30 min or less then G18 should be "0"
IF F18 is greater than 00:30:00 then G18 should be the difference between 00:30:00 and F18
In this case, since F18 is equal to 1 hour taken so G18 should show the difference of 30 minutes. If F18 was 35 mins then G18 would be the difference of (35-30) = 5mins etc.

Any help would be GREATLY appreciated, I also attached an excel sheet if that would make things easier.

Thank you!

 

[car1oph]

if you convert the data in column F to the number of minutes that the break lasted, in column G you can just write a conditional, which results in the number of minutes for which you executed the 30 minutes allowed, I attatch the file where I did it

 

[pecoflyer]

Like this ?
BTW avoid merged cells , they are nothing but trouble

 

[DataFolks]

Thank you both.