Total hours and paid hours calculation advice

Hi Guys i need a bit of advice please on how to fix this issue i'm having.

Basically attached is a rota for working over a 2 week period, initially it was 1 box with the days working hours wrote inside, but i wanted to make the Total calculable at the end, in case the manager changes any hours so we know exactly what we have worked.

in the total hours i have used a formula like

Please Login or Register  to view this content.

i had to use the divide by 100 at the end due to the total having two 0's at the end due to one hour being 100, and 30 mins as 50, through excel calculations. and also used that formula so if the manager adds extra hours in the blank boxes (our days off) it will automatically calculate.

However in the paid total i'm really struggling to get this to work. on 10 hours shifts we get 1 hour lunch however 30 mins is paid and 30 unpaid. and 8 hour shift is 30 minutes unpaid. So in theory every shift we work we would just lose 30 minutes for lunch, s

i know if i had a separate box for lunch on the rota then it would be easy to calculate. but as i cant really change the way it looks on here, im forced to find an alternative way.

i have tried the above formula again but added =SUM($D4-$c4)-50,($F4-$E4)-50 etc etc, but i keep getting an error. Also i thought afterwards if i did get it to work, the issue might be the blank boxes then, as there will be no time to subtract 50 off so it would be a value of -50 instead of a zero value.

can anyone advise on how i can fix this? i've attached the file so you guys can have a look

thank you

I cut away the extra rows and focused on one. you can fill out what you need from there.

I added conditional formatting so you can see what days are staffed easily. the calculation steps are at the end which you can hide.

The countif counts any day that is staffed (but it counts both start and end times hence the /2) this gives you a multiplier to find the amount to deduct from working hours for lunches.

***i see that now, im not sure why its not working. the Countif should capture every field above 0.***

im a dummy, i have it backwards change the countif to >0 (for some reason i have it set to >= )

I have tested it by changing all times to 00:00 to get a 0 and all times to 09:00/17:00 to get 14 so it should be working

i removed the = from the >=0 but its throwing out a 1 in the box at the end if i add hours on a scheduled day off. and if i still leave the = in, i still only get 7... hmmmmm how did you get it to work after you insulted yourself by saying your a dummy :D when your clearly not :D
edit: i dont know if its the wednesday causing the problem as its a different custom. however ive chopped and changed but the best ive done is getting it to recognise days off rather than on. any ideas mate?