+ Reply to Thread
Results 1 to 7 of 7

Calculating hours worked per day with multiple timestamps

  1. 06-02-2017, 12:18 AM #1
    rsilberfarb
    rsilberfarb is offline
    Registered User
    Join Date
    06-02-2017
    Location
    Seattle, WA USA
    MS-Off Ver
    365
    Posts
    2

    Calculating hours worked per day with multiple timestamps

    Hello,

    I have tried to figure this out but cannot. I simply need to find total hours worked per day from the sample below using a formula or pivot table or advanced filter (i have a lot of data to go through). Basically just need to figure out how to parse this down to just 2 entries per day (earliest timestamp and latest timestamp) I have tried subtotals, min, max, but cannot get it quite right. Any help is greatly appreciated!!!!

    Ross

    Date Time
    25-Aug-08 1:53:44 PM
    27-Aug-08 4:36:00 AM
    28-Aug-08 7:34:07 AM
    29-Aug-08 9:53:35 AM
    29-Aug-08 9:55:13 AM
    29-Aug-08 9:57:51 AM
    29-Aug-08 9:58:41 AM
    29-Aug-08 11:15:16 AM
    30-Aug-08 3:33:00 PM
    2-Sep-08 8:01:07 AM
    2-Sep-08 8:51:14 AM
    2-Sep-08 11:47:55 AM
    2-Sep-08 2:26:30 PM
    3-Sep-08 9:28:53 AM
    3-Sep-08 9:30:11 AM
    3-Sep-08 9:31:00 AM
    3-Sep-08 9:57:00 AM
    3-Sep-08 11:20:45 AM
    3-Sep-08 11:21:01 AM
    3-Sep-08 4:07:17 PM
    3-Sep-08 4:29:54 PM
    3-Sep-08 5:07:02 PM
    3-Sep-08 5:17:07 PM
    3-Sep-08 5:18:19 PM
    3-Sep-08 5:18:42 PM
    3-Sep-08 5:18:55 PM
    3-Sep-08 5:19:13 PM
    Reply With QuoteRegister To Reply
  2. 06-02-2017, 01:57 AM #2
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Calculating hours worked per day with multiple timestamps

    Assuming the data starts in A2, you need to expand the data set for analysis via Pivot Table.

    B1: Date
    C1: Time
    B2: =INT(A2) (formatted as date)
    C2: =MOD(A2, 0) (formatted as time)

    Copy down.

    Now you can easily create a pivot table showing DATES as row values, the put TIME in as column values twice, once with MIN and once with MAX.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  3. 06-02-2017, 02:04 AM #3
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Calculating hours worked per day with multiple timestamps

    Or this:
    In C2: (ARRAY FORMULA- press CTRL+SHIFT+ENTER to confirm, then DRAG down.)
    =IFERROR(INDEX(INT($A$2:$A$28),MATCH(0,COUNTIF($C$1:$C1,INT($A$2:$A$28)),0)),"")

    In D2: (ARRAY FORMULA- press CTRL+SHIFT+ENTER to confirm, then DRAG down.)
    =(MAX(IF(INT($A$2:$A$28)=C2,$A$2:$A$28))-MIN(IF(INT($A$2:$A$28)=C2,$A$2:$A$28)))*24

    Untitled.png

    Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
    Last edited by leelnich; 06-02-2017 at 07:07 PM.
    Reply With QuoteRegister To Reply
  4. 06-02-2017, 04:21 AM #4
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Calculating hours worked per day with multiple timestamps

    Withdraw-LLN
    Last edited by leelnich; 06-02-2017 at 04:23 AM.
    Reply With QuoteRegister To Reply
  5. 06-02-2017, 06:22 PM #5
    rsilberfarb
    rsilberfarb is offline
    Registered User
    Join Date
    06-02-2017
    Location
    Seattle, WA USA
    MS-Off Ver
    365
    Posts
    2

    Re: Calculating hours worked per day with multiple timestamps

    leelnich-Thank you for the reply. This is doing exactly what i needed, it is just taking my computer 20 minutes to process after I drag down to all the records.

    Ross
    Reply With QuoteRegister To Reply
  6. 06-02-2017, 07:16 PM #6
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Calculating hours worked per day with multiple timestamps

    Yep, ARRAY FORMULAE are sometimes slow, but it sounded like you only had to do it once. After calculation, copy those cells and paste as values, so you never have to sit thru it again.
    Reply With QuoteRegister To Reply
  7. 06-02-2017, 07:45 PM #7
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Calculating hours worked per day with multiple timestamps

    the solution in post #2 is non-array, so should be fine for realtime calculation after installed.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Calculating Hours Worked Past Midnight - Without no time entries equating to 24 hours.
    By mttomkins in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-13-2017, 11:05 AM
  2. Calculating time difference between two timestamps during buisness hours
    By Joo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-16-2013, 03:32 PM
  3. [SOLVED] Finding Individuals on Multiple Sheets, then Calculating Hours Worked
    By LTExcel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-08-2012, 11:05 PM
  4. [SOLVED] Punch-Time Data. Calculating # of hours worked and # of hours worked alone per shift.
    By Daga3 in forum Excel General
    Replies: 0
    Last Post: 05-14-2012, 05:36 PM
  5. 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
  6. Excel 2007 : Calculating basic hours worked, between a time range and premium hours worked
    By RoyLittle0 in forum Excel General
    Replies: 0
    Last Post: 01-05-2012, 06:23 AM
  7. Calculating Man hours and days worked over multiple sheets
    By MacMS in forum Excel General
    Replies: 4
    Last Post: 07-15-2010, 11:48 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1