+ Reply to Thread
Results 1 to 6 of 6

Pivot Table - Calculating total hours worked with multiple employees and multiple log-ins

  1. #1
    Registered User
    Join Date
    09-12-2017
    Location
    New York, NY
    MS-Off Ver
    Office 2016 Standard
    Posts
    3

    Pivot Table - Calculating total hours worked with multiple employees and multiple log-ins

    Please see the attached spreadsheet/pivot. I've spent hours searching for a solution to what seems to be a simple problem. I'd like to add a column to this pivot that calculates the difference between the first login and the last logout (existing columns) - ultimately providing the number of hours spent "in the system" or at work. I can't get calculated fields to work when using the Min and Max formulas. Nor does the calculated item work.

    Thank you,

    anthonycoz
    Attached Files Attached Files

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    IXL,BXL,BE,EU,Earth
    MS-Off Ver
    2k16(64) not4subs
    Posts
    4,101

    Re: Pivot Table - Calculating total hours worked with multiple employees and multiple log-

    Maybe like this Calculated Field. You can change to Hours or Seconds or any other format

    You can re-build this CF about IF(LogIn=LogOut,0,INT(....
    I assumed 1 not 0 because it's impossible to LogIn and LogOut in the same exactly time. There will be difference always, maybe a few 1/10000 secs but will be.
    What you will do it's up to you
    Attached Files Attached Files
    Last edited by sandy666; 09-12-2017 at 07:44 PM.
    sandy (NO CAPS)
    Excel can do everything but still doesn't read user's wishful thinking
    The ready made solution will not teach anyone anything

    A logical description of the problem is the basis of dialogue and a rapid solution. Nobody sits in your head to understand your way of thinking
    Note: English is not my native language
    Keep It Sophisticatedly Simple

  3. #3
    Registered User
    Join Date
    09-12-2017
    Location
    New York, NY
    MS-Off Ver
    Office 2016 Standard
    Posts
    3

    Re: Pivot Table - Calculating total hours worked with multiple employees and multiple log-

    Thank you Sandy666, but your suggestion is the result I've been getting as well, and unfortunately it's not what I need. This shows the difference in minutes adding ALL logins/logouts. What I need is the difference in minutes (hours) from the 1st login to the last logout. See my new uploaded sample. Ultimately I need to subtract the FIRST time an employee logs in (LogIn) from the LAST time an individual logs out (BackHomepage).

    The example I've updated in the spreadsheet shows employee ID 8544 with three login/logouts on one day (April 28, 2017). the pivot adds all of those together to arrive at 517 minutes, however, what I need to capture is actually 540 minutes - the time difference between the FIRST login and the LAST logout. I hope this is more clear.

    Thank you again.

    anthonycoz
    Attached Files Attached Files

  4. #4
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    IXL,BXL,BE,EU,Earth
    MS-Off Ver
    2k16(64) not4subs
    Posts
    4,101

    Re: Pivot Table - Calculating total hours worked with multiple employees and multiple log-

    I see, maybe Calculated Item will help but this will not be repeatable for ranges of dates

  5. #5
    Registered User
    Join Date
    09-12-2017
    Location
    New York, NY
    MS-Off Ver
    Office 2016 Standard
    Posts
    3

    Re: Pivot Table - Calculating total hours worked with multiple employees and multiple log-

    So, we're stuck at this point? How do you suggest I proceed with finding the answer? Apologies, I'm new to these forums, do I re-post for someone else to view?

  6. #6
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    IXL,BXL,BE,EU,Earth
    MS-Off Ver
    2k16(64) not4subs
    Posts
    4,101

    Re: Pivot Table - Calculating total hours worked with multiple employees and multiple log-

    You can try with OFFSET() with condition(s) and some more function inside on source table. Maybe someone else will give you working formula(s)
    or
    You can try on VBA part of forum with good, detailed description (but not Romeo and Juliet story) or new thread there with the link to this thread.

    Thread is public so everyone see it

    btw. create clean example excel file with what you've and what you want (BEFORE / AFTER). Not what you think how it should be done.
    Last edited by sandy666; 09-13-2017 at 02:20 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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