2

I've researched through the site to see if this has been answered but with no luck after reading:

In Google Sheet I'm trying to measure time accounting for a break:

enter image description here

When I add the first start time I get a negative:

enter image description here

when I add a clock out it's ok:

enter image description here

Starting the time back up it throws that time off:

enter image description here

How can I calculate the time that has an IN and OUT but omit the time when it only has an IN value?

The formula:

=ROUND((ARRAYFORMULA(IF(ISBLANK(B3:C3)=FALSE,((C3-B3)*24*60/60),""))) + ARRAYFORMULA(IF(ISBLANK(D3:E3)=FALSE,((E3-D3)*24*60/60),"")),2)
1

2 Answers 2

0
  • to prevent negative value you can MAX it:

=MAX(ROUND(ARRAYFORMULA(IF(ISBLANK(B3:C3)=FALSE,((C3-B3)*24*60/60),"")) 
         + ARRAYFORMULA(IF(ISBLANK(D3:E3)=FALSE,((E3-D3)*24*60/60),"")),2),0)

  • to fix the logic:

={IF(E3<>"",ROUND(ARRAYFORMULA(IF(ISBLANK(B3:C3)=FALSE,((C3-B3)*24*60/60),"")) 
                + ARRAYFORMULA(IF(ISBLANK(D3:E3)=FALSE,((E3-D3)*24*60/60),"")),2),
            ROUND(ARRAYFORMULA(IF(ISBLANK(B3:C3)=FALSE,((C3-B3)*24*60/60),"")),2))}

0

Considering that Google Sheets can do additions and subtractions of date, time and durations, the formula looks to be convoluted, as shown in the question looks to be complicated.

There is no need to use ARRAYFORMULA, IF, ROUND, multiplication and division operations.

=MAX(0,C3-B3)+MAX(0,E3-D3)

To set the cell formatting to Duration, do the following:

  1. Click Format > Number > Duration

You might use Format > Number > Custom date and time to refine the format, i.e., remove the seconds part.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.