libreoffice - LibreCalc Calculate Time Differential -

i have following spreadsheet:

i need take time in (c2) , time out (f2) , find out how many hours between 6pm , 6am has been worked.

so if employee clocks in @ 4pm , works till 1am field j2 should read 7 second shift differential.

i have tried many formulas , know 1 have not right either. because merely subtracting 2 dates , giving result hence -12 there now.

thanks in advance folks!

the standard formula

min(t1upper,t2upper)-max(t1lower,t2lower)

where t1lower start of night rate, t1upper end of night rate, t2lower start of shift , t2upper end of shift.

but in case have add 1 (i.e. 24 hours) finish time if it's in small hours: end of night rate put in 30:00.

i've put start , end of night rate a2 , b2 formula is

=min($b$2;if(f2<d2;f2+1;f2))-max($a$2;d2)

or shorter

=min($b$2;f2+(f2<d2))-max($a$2;d2)

(tested in openoffice calc - i'm assuming librecalc same)

there 2 cases haven't considered (sorry)

(1) shift entirely during daytime hours there no shift differential , result turn out negative need treat zero:-

=max(min($b$2;if(f2<d2;f2+1;f2))-max($a$2;d2);0)

(2) shift starts between midnight , 6am need consider range 0:00-6:00 18:00-30:00. putting gives:-

=max(min($b$2;f2)-max($a$2;d2);0)+max(min($b$3;if(f2<d2;f2+1;f2))-max($a$3;d2);0)

where a2 contains 0:00, b2 contains 6:00, a3 contains 18:00 , b3 contains 30:00

Ben