# Calculating daily totals (on one sheet) and annual totals (across different sheets)

1. ## Calculating daily totals (on one sheet) and annual totals (across different sheets)

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!  Register To Reply

2. ## Re: Calculating daily totals (on one sheet) and annual totals (across different sheets)

copy paste below in D2 of jan then hold control and shift then hit enter to make it array formula and drag down , apply formatting as h:mm
Formula:  `Please Login or Register  to view this content.`

same formula can be applied to feb sheets and so on on D2  Register To Reply

3. ## Re: Calculating daily totals (on one sheet) and annual totals (across different sheets)

Thank you! That worked well for the first part! Do you have any advice for the 2nd part? Is it possible?  Register To Reply

4. ## Re: Calculating daily totals (on one sheet) and annual totals (across different sheets)

can you update a sample sheet for the second case how you want it to be  Register To Reply

5. ## Re: Calculating daily totals (on one sheet) and annual totals (across different sheets)

Thanks again for replying! It's already in the sample worksheet. Under sheet "Jan" it is cell D15, and under sheet "Feb" it is cell D7.

Ideally, when I then duplicate sheet "Feb" to create "March" it will automatically reference the previous sheet (now "Feb") to calculate the annual running total, even though the number of entries each month is going to be different.  Register To Reply

6. ## Re: Calculating daily totals (on one sheet) and annual totals (across different sheets)

on feb sheet type Jan and Feb in I1 and I2
then on G1 you can copy paste below then hold control and shift then hit enter to make it array formula
Formula:  `Please Login or Register  to view this content.`

now on march sheet in I3 put sheet name and change the formula red part to 3 and so on for each sheet you create
Other than that I think you can use worksheet change event  Register To Reply

7. ## Re: Calculating daily totals (on one sheet) and annual totals (across different sheets)

Thanks for that! Does there seem to be a way to make a function do all that without having to manually adjust each sheet?  Register To Reply