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?