Google-sheets – How to prevent or omit a negative value when calculating between times

google sheetsgoogle-sheets-dates

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)

Best Answer

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

Related Topic