+ Reply to Thread
Results 1 to 2 of 2

Sum hours worked between time frame based on punch times and criteria

  1. #1
    Registered User
    Join Date
    04-09-2020
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    1

    Sum hours worked between time frame based on punch times and criteria

    First post here; hello everyone,

    I am stumped and need some help from you guys. I posted on Stack and Reddit with no help from anyone other than "use SUMIFS" or "use Pivot Table" which does not help me at all given I can't seem to understand how those could even work with my dataset.

    I have a list of Punch Times (Sheet2) with their respective Departments:

    orQfg.png

    I'm trying to figure out a way to get the data summed/consolidated into this format (Sheet1):

    ximhJ.png

    The solution for C5 would sum the total number of hours worked between 12 AM and 1 AM (using Punch Times on Sheet2 Col G & I) on Sundays (Sheet2 Col K) for Department 300 (Sheet2 Column R) stated in Sheet1 B2.

    Note: Punch Out Time cannot go past 12 AM. If they work past 12 AM a new record will start underneath with 12 AM as the Punch In Time.

    I can write a formula to grab the total time for each individual record and time frame, but I don't want to add 20+ columns to the dataset and rather sum all the records in one cell (Sheet1 C5).

    Is there any way to do this with a single formula or maybe even a pivot table solution?

    EDIT: The first record would be recorded as 1 in each cell from I12:I19.

    Thanks!
    Last edited by spacej3di; 12-15-2020 at 03:57 PM.

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Sum hours worked between time frame based on punch times and criteria

    You really think someone can help you without seeing the excel file?
    Look at the yellow banner at the top of page for more instrctions.

+ 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] Calculating multiple pay rates based on time of day worked, not number of hours worked
    By vdbonce in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 03-04-2020, 02:30 AM
  2. Replies: 1
    Last Post: 07-13-2019, 01:12 PM
  3. [SOLVED] Calculating time worked based on times in and out
    By nickpavlov in forum Excel General
    Replies: 5
    Last Post: 06-05-2019, 03:41 PM
  4. Clock in multiple punch in/punch out times Formula
    By Schnizzle74 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-24-2014, 06:15 PM
  5. Replies: 0
    Last Post: 05-14-2012, 05:36 PM
  6. Calculating basic hours worked, between a time range and premium hours worked
    By RoyLittle0 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 01-07-2012, 06:59 AM
  7. Replies: 0
    Last Post: 01-05-2012, 06:23 AM

Tags for this Thread

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