# Shortening this Formula, and Future Proofing it

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?

• 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

# Static:

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

paste in C31 cell and drag to the right:

``````=ARRAYFORMULA(SUM(QUERY(IF(ISNUMBER( 