if or iferror or other formula help needed to count idle hours.

Hello,

I am maintaining a worksheet that has multiple workers' login data and working time. To save time and fast calculation, I have used some formulas.
But I am unable to maintain it perfectly and need your help to solve it.
If formula.png

Attached image the sample of one of my team members daily summary. I am using this formula in M87 but this is not perfect.
=if(((K87+L87)<0.375),(0.375-(K87+L87)))+if(((K87+L87)>0.375),0)

I need,
1. if the shift (B column) is "day off" or "Sick leave" then "M" will be 0 or if the sum of work and Training (column k87 and L87) is "0" than M will be counted "0:00:00"
2. if the shift (B column) is "Evening" or "Morning" or if the sum of work and Training (column k87 and L87) is more than 9 hours than M will be counted "0:00:00"
3. if the shift (B column) is "Evening" or "Morning" or if the sum of work and Training (column k87 and L87) is less than 9 hours than M will be counted "9-(K87+L87)"

above formula only fulfill the 2nd and 3rd condition, but not the 1st condition.

Thanks in advance,
Best,
Rinko

Hi,

Sorry for late response and incomplete forum posting. I have attached an excel work. Please take a look.

Best,
Rinko.

K87 & L87 are blank cells???

Oh, I just removed those data from the bottom as I include 2 sample. K87 was same of k9 or k48.
Formula will be - =if(((K9+L9)<0.375),(0.375-(K9+L9)))+if(((K9+L9)>0.375),0).

Best,
Rinko.