I am creating a Google sheet to use as an employee calendar. On this sheet, I have cells that will contain the total number of hours worked per day by all employees.
For the moment, the formula that I have configured for these cells is as follows:
= (IF (ISDATE (D16), D16) -IF (ISDATE (C16), C16)) + (IF (ISDATE (D17), D17) -IF (ISDATE (C17), C17)) ... etc.
The reason for the
SI (ISDATE) controls, are because the cells on the calendar contain a
X value if the employee is not available those days, or
FREE TIME if the employee has reserved free time.
For these equations also, column C represents the arrival time and column D the time of the clock output.
My problem with this at the moment is that if I add a new line, for example by hiring a new employee, I have to manually go into the cell and add the Row values to it.
For example, if I added a new line to what I wrote above and that created line 18, I should add manually:
+ (SI (ISDATE (D18), D18) -IF (ISDATE (C18), C18))
to the formula. This can be tedious and it can be difficult to verify that everything is added correctly.
Is there a way for me to make this formula a kind of fan to prove in the future what I'm trying to do?
EDIT: Here is a link to the Google Card