CODEWITHSUNDEEP
excelgoogle-sheetsexcel-formulaspreadsheetformulas
Bert Starington
Bert Starington

Reputation: 61

Google sheets, Subtracting breaks from a range of hours

Say cell A3 ='12:00 PM' and B3 ='6:00 PM'

This formula will return '6', for 6 hours worked.

=IF(ISERROR((TIMEVALUE(B3)-TIMEVALUE(A3))*24),"0",(TIMEVALUE(B3)-TIMEVALUE(A3))*24)

However, I want the formula to subtract breaks for working a certain rage of hours. Say for example:

What could I add to the code above to achieve this?

Upvotes: 1

Views: 288

Answers (2)

Chris Hick
Chris Hick

Reputation: 3094

Does this formula work as you want:

=IF(ISERROR((TIMEVALUE(B3)-TIMEVALUE(A3))*24),"0",(TIMEVALUE(B3)-TIMEVALUE(A3))*24)-LOOKUP((TIMEVALUE(B3)-TIMEVALUE(A3))*24,{0,0;5,0.5;7,1})

Upvotes: 2

vk2015
vk2015

Reputation: 1138

You can use nested if statements - this is sloppy but you could copy and paste into the same cell (but maybe change the row number depending on where the data starts):

=if(if(iserror((timevalue(B1)-timevalue(A1))*24),"0",(timevalue(B1)-timevalue(A1))*24)<4.5,if(iserror((timevalue(B1)-timevalue(A1))*24),"0",(timevalue(B1)-timevalue(A1))*24),if(and(if(iserror((timevalue(B1)-timevalue(A1))*24),"0",(timevalue(B1)-timevalue(A1))*24)>5,if(iserror((timevalue(B1)-timevalue(A1))*24),"0",(timevalue(B1)-timevalue(A1))*24)<7),if(iserror((timevalue(B1)-timevalue(A1))*24),"0",(timevalue(B1)-timevalue(A1))*24)-0.5,if(if(iserror((timevalue(B1)-timevalue(A1))*24),"0",(timevalue(B1)-timevalue(A1))*24)>=7,if(iserror((timevalue(B1)-timevalue(A1))*24),"0",(timevalue(B1)-timevalue(A1))*24)-1,if(iserror((timevalue(B1)-timevalue(A1))*24),"0",(timevalue(B1)-timevalue(A1))*24))))

A cleaner way to do this would be to create additional columns for each break interval (e.g., column C still calculates the unadjusted working time, but add column D that equals -0.5 if column C is between 5 and 7, and add column E which equals -1 if column C is greater than 7, then column F adds Columns C, D, and E)

Upvotes: 0

Related Questions