1

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)
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))}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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