Search code examples
google-sheetsarray-formulas

I'm trying to further automate calculations on timesheets, depending on gaps sizes between jobs

I am trying to automate a timesheet. The employee gets paid for gaps of 30 minutes or less. They do not get paid for gaps of greater than 30 minutes. They may do 1 job per day or as many as 6 jobs per day. Therefore the calculation is not just from their start time to their finish time each day.
enter image description here The last column in the image shows my desired result. https://docs.google.com/spreadsheets/d/1wcZ2k3pc5t7Rco688Ja2LrP4PIkOpwcA5IjWBQvS2Gs/copy

Solution

  • delete range I13:K and use in cell I13:

    =ARRAYFORMULA(IF(H13:H="",,ROUND((H13:H-G13:G)*24, 2)))

    use in cell J13:

    =ARRAYFORMULA(IF((A13:A<>"")+(H13:H=""),,ROUND((G13:G-H12:H)*24, 2)))

    and use in cell K13:

    =ARRAYFORMULA(IF(H13:H="",,I13:I+IF(J13:J<=0.5, J13:J, )))

    enter image description here