+ Reply to Thread
Results 1 to 7 of 7

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

  1. #1
    Registered User
    Join Date
    03-01-2020
    Location
    USA
    MS-Off Ver
    365
    Posts
    11

    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!
    Attached Files Attached Files
    Last edited by vdbonce; 03-05-2020 at 04:00 AM.

  2. #2
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    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: copy to clipboard
    Please Login or Register  to view this content.


    same formula can be applied to feb sheets and so on on D2
    Attached Files Attached Files
    Last edited by hemesh; 03-05-2020 at 04:14 AM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  3. #3
    Registered User
    Join Date
    03-01-2020
    Location
    USA
    MS-Off Ver
    365
    Posts
    11

    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?

  4. #4
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    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

  5. #5
    Registered User
    Join Date
    03-01-2020
    Location
    USA
    MS-Off Ver
    365
    Posts
    11

    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.

  6. #6
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    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: copy to clipboard
    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

  7. #7
    Registered User
    Join Date
    03-01-2020
    Location
    USA
    MS-Off Ver
    365
    Posts
    11

    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?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Adding Annual Totals on a Summary Sheet by Name
    By gloriousglenn in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-06-2019, 02:12 PM
  2. Calculate Weekly totals in separate sheet, from daily totals
    By cassar in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-10-2018, 12:50 AM
  3. Replies: 11
    Last Post: 08-12-2015, 01:38 PM
  4. calculating daily totals
    By silverbirch in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-29-2013, 09:57 AM
  5. [SOLVED] How do I group daily totals into weekly/monthly/quarterly totals
    By situationroom in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-28-2013, 09:58 AM
  6. Method for calculating Invoice Totals and Grand totals
    By sahran4441 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-06-2010, 10:24 PM
  7. Replies: 4
    Last Post: 01-10-2008, 12:31 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1