In the sample, each month is a sheet with dates and hours worked, with potentially multiple entries on each day.
1. Trying to to calculate the total time from first clocking in to last clocking out.
Essentially, I am trying to come up with a formula for column D that says:
If the row above has the same date as the current row, go one more row above, and if different date, then return the start time of the row (which is the first of the date). Then, subtract that time from the end time of the current row (which is the last of the date).
I do have iterative functions turned on so that the formula can self-reference if necessary, but I am not sure how to make the function keep going after one instance of OFFSET($D3,-1,).
=IF($A2=OFFSET($A2,-1,), GO ONE MORE ROW ABOVE AND REPEAT TEST, $C2)
2. Then at the bottom of each sheet, would like to have running tally of hours worked for the year to date.
I was able to find how to SUM() across multiple sheets, but this seems to require referencing a specific cell on each sheet, whereas in this case, the row containing the relevant information changes depending on the number of entries for the month. Is there any way to get the reference to stick? (Kind of like when on the same sheet, if you delete a row or column the formulas will not get messed up, but not sure how to achieve this across multiple sheets.)
Also, ideally, when the master template is copied to generate more months of data, the function would SUM all sheets from "Jan" to "current sheet" regardless of what the name of the sheet is. Is this even possible?
Thanks for any insight!
Bookmarks