Calculation of entry / exit working hours by reporting minutes / hours missing from a tota

Hello, I would like your help to find a formula for calculating missing hours in the working time between entry and exit which can be on 2 or 4 columns.
In the attached file you will find the standard hours 08.00-14.00 - 14.30-17.30
total 09.00 hours.

All missing hours and minutes during this period must be reported.

From 14.00 to 14.30 Lunch break should not be considered.

Some examples 08.10-14.30 are missing 03.10
* 9 total hours less from 08 to 14 are 6 hours remains 3 hours
however, we entered at 08.10 so 10 warnings must be marked = 3.10

Other examples in the file with IF formulas inserted
but, to find all the initiations with IF, it becomes too long.
Thank you

Probably I've made it too complicated, but it seems to work fine. The formula do not use IFs - Uses MAX and MIN instead.
so we have in G4 (and copied down):

Formula:

Please Login or Register  to view this content.

breaking it into pieces (you may test these one by one) we have:

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

is total expected time (9hrs)

Formula:

Please Login or Register  to view this content.

is total time from check-in to check-out

Formula:

Please Login or Register  to view this content.

is the length of break between 2:00pm and 2:30pm (if it is included in reported working hours
and

Formula:

Please Login or Register  to view this content.

is a correction for late start (column D) after the break (if taken)

Here is single simple formula.
In H4 then copied down.

Please Login or Register  to view this content.

Please try at H5
=ROUND(9/24-(MIN(C5,$E$4)-MAX(B5,$B$4))+MAX(0,MIN($D$4,C5)-MAX($C$4,B5))-(MIN($E$4,E5)-MAX($D$4,D5))*(D5>0),5)

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Try

Formula:

Please Login or Register  to view this content.

Remember to use i with accent (not a dot) - unfortunately oon this website it is replaced with standard i

Upload file with results with problem highlighted

How J10=2 and J11=3.
In H4 then copied down.

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

My proposition:

Formula:

Please Login or Register  to view this content.

Please try at I4
=(A4="Marted́")*$L$1-((B4>0)*(MIN(C4,$P$1)-MAX(B4,$M$1)-MAX(0,MIN(C4,$O$1)-MAX(B4,$N$1)))+(D4>0)*(MIN(E4,$P$1)-MAX(D4,$M$1)-MAX(0,MIN(E4,$O$1)-MAX(D4,$N$1))))

Glad to see thread solved and thanks for reputation points

I hope we covered most of possible situations. Two exceptions I'm aware of: somebody writen only second part (columns D:E while B:C left empty) or the work finished (columns C or E) after midnight