Search code examples
libreofficelibreoffice-calc

LibreCalc Calculate Time Differential

I have the following spreadsheet: enter image description here

I need to take Time in (C2) and Time Out (F2) and find out how many hours between 6pm and 6am has been worked.

So if Employee clocks in at 4PM and works till 1AM then field J2 should read 7 for the second shift differential.

I have tried many formulas and I know the one I have is not right either. Because it is merely subtracting the 2 dates and giving the result hence the -12 that is there now.

Thanks in advance as always folks!

Solution

  • The standard formula is 

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

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

    But in this case you have to add one (i.e. 24 hours) to the finish time if it's in the small hours: also the end of the night rate is put in as 30:00.

    I've put the start and end of the night rate into A2 and B2 so my formula is

    =MIN($B$2;IF(F2<D2;F2+1;F2))-MAX($A$2;D2)

    or slightly shorter

    =MIN($B$2;F2+(F2<D2))-MAX($A$2;D2)

    (tested in OpenOffice Calc - I'm assuming LibreCalc will be the same)

    enter image description here

    There are two cases I haven't considered (sorry)

    (1) The shift is entirely during daytime hours so there is no shift differential and the result will turn out to be negative so you need to treat it as zero:-

    =MAX(MIN($B$2;IF(F2<D2;F2+1;F2))-MAX($A$2;D2);0)

    (2) The shift starts between midnight and 6am so you need to consider the range 0:00-6:00 as well as 18:00-30:00. Putting this all together 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 now contains 0:00, B2 contains 6:00, A3 contains 18:00 and B3 contains 30:00

    enter image description here