1

I am creating a Google Sheet to use as an employee schedule. On this sheet, I have Cells which will contain the Total number of Hours worked per day by all employees.

At the moment the formula I have set up for these Cells are as follows:

=(IF(ISDATE(D16), D16)-IF(ISDATE(C16), C16))+(IF(ISDATE(D17),D17)-IF(ISDATE(C17), C17))...etc

The reason for the IF(ISDATE) checks are because the cells on the schedule contain an X value if the employee isn't available on those days, or TIME OFF if the employee has booked the time off.

For these equations as well, Column C is the Clock In time, and Column D is the Clock Out time.

My issue with this at the moment is if I add a new row, from hiring a new employee, for example, I need to manually go into the cell and add the Row values to it.

For example, if I added a new row to what I wrote above and it created Row 18, I'd need to manually add:

+(IF(ISDATE(D18), D18)-IF(ISDATE(C18), C18))

to the formula. This can be tedious and can make it difficult to check that everything is being added correctly.

Is there a way for me to set up this formula as some sort of range as a way to future proof what I am trying to do?

EDIT: Here is a link to the Google Sheet

  • can you share a copy of your sheet? – user0 Apr 29 at 22:51
  • @user0 Just edited the OP with a link – Skitzafreak Apr 29 at 22:55
1

Static:

=ARRAYFORMULA(SUM(QUERY(IF(ISNUMBER(
 INDIRECT("E16:F"&ROW()-1)), 
 INDIRECT("E16:F"&ROW()-1), ), 
 "select Col2-Col1")))

0


Dynamic:

paste in C31 cell and drag to the right:

=ARRAYFORMULA(SUM(QUERY(IF(ISNUMBER(
 INDIRECT(ADDRESS(16, COLUMN(), 4)&":"&ADDRESS(ROW()-1, COLUMN()+1, 4))), 
 INDIRECT(ADDRESS(16, COLUMN(), 4)&":"&ADDRESS(ROW()-1, COLUMN()+1, 4)), ), 
 "select Col2-Col1")))

0

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.