CODEWITHSUNDEEP
google-sheetsformulaspreadsheet
Gerry
Gerry

Reputation: 11

Calculate working time subtracting lunch time if needed

I'm struggled with a spreadsheet that contains how much time I have been working. I have in each row the start time and end time of each working day.

Example sheet:

|    | A               | B      | C          |  
----------------------------------------------  
|1   |Start time       |End time|Total time  |  
|2   |7:00             |13:30   |6:00        |  
|3   |7:00             |10:00   |3:00        |

Shared table

The issue is that the total time should not consider lunch time. I want to set up a sheet that will automatically take out the half hour for lunch when the total time is greater than 4 hours. For example, if the start time is 7:00 and the end time is 13:30, I want to get 13:30-7:00 that is 6:30 minus 0:30=6:00. However, if the start time is 7:00 and the end time is 10:00, I want to get 10:00-7:00, that is 3:00. I tried =SUM(-(A2-B2)*24) but without success.

The expected output is in red. I want to obtain such output using formulas.

Thank you!!!!

Upvotes: 0

Views: 1246

Answers (1)

JPV
JPV

Reputation: 27282

Formula to be dragged down:

=mod(B2-A2;1)-if(mod(B2-A2;1)>time(4;0;0); time(0;30;0); 0)

Because of the mod() function this formula should deal with 'negative' hours in case of overnight time.

Or in arrayformula:

=ArrayFormula(if(len(B2:B); mod(B2:B-A2:A;1) - if(mod(B2:B-A2:A;1)>time(4;0;0); time(0;30;0); 0);))

Format the output column as 'duration'...

Upvotes: 0

Related Questions