+ Reply to Thread
Results 1 to 8 of 8

Time Calculation - What is the easiest way to calculate hours worked per day?

  1. #1
    Registered User
    Join Date
    11-27-2014
    Location
    Chennai
    MS-Off Ver
    2007
    Posts
    13

    Question Time Calculation - What is the easiest way to calculate hours worked per day?

    I have a xls detailing employee time swipes through a card reader. Unfortunately, all of the time data is in a single column. What is the easiest way to calculate hours worked per day? I'm looking at all data for all employees from January 2011 through February 2011, so it's a large set to have to parse manually.

    Below is a sample attachec. There are three columns: Card ID, Time, and a column detailing whether it was a swipe into the building or out of the building. What is the easiest way to calculate hours worked per day?

    pic.jpg

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Time Calculation - What is the easiest way to calculate hours worked per day?

    Attach an Excel file - no-one will want to type that data into Excel themselves to create a mock-up to work on.

    Pete

  3. #3
    Registered User
    Join Date
    11-27-2014
    Location
    Chennai
    MS-Off Ver
    2007
    Posts
    13

    Re: Time Calculation - What is the easiest way to calculate hours worked per day?

    Sorry for inconvenience. Please download the same from below link.

    https://www.dropbox.com/s/tnjx7jyrpg...data.xlsx?dl=0

  4. #4
    Registered User
    Join Date
    11-27-2014
    Location
    Chennai
    MS-Off Ver
    2007
    Posts
    13

    Re: Time Calculation - What is the easiest way to calculate hours worked per day?

    Quote Originally Posted by Pete_UK View Post
    Attach an Excel file - no-one will want to type that data into Excel themselves to create a mock-up to work on.

    Pete
    Please download the same from below link.

    https://www.dropbox.com/s/tnjx7jyrpg...data.xlsx?dl=0

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Time Calculation - What is the easiest way to calculate hours worked per day?

    As the card numbers in the example are all the same, I presume this is just for one employee for a short period of time. How would you envisage the data being presented after processing? Do you want dates going across and the card numbers going down, with the hours worked for each day for each card number shown in tabular format? Do you want to select, say, a month of data at a time, or by week/starting date? Please advise.

    By the way, you can attach files directly to the Forum - click on Go Advanced while composing or editing a post, then use the Paperclip icon or scroll down to Manage Attachments, then follow the sequence (the FAQ describes this in more detail).

    Hope this helps.

    Pete

  6. #6
    Registered User
    Join Date
    09-01-2011
    Location
    georgia
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: Time Calculation - What is the easiest way to calculate hours worked per day?

    Would this work for you? Calculate the difference in every other line then sum the total.

    HoursWorked.xlsx

    Edit: shg has a more elegant solution to calculate total in one cell

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Time Calculation - What is the easiest way to calculate hours worked per day?

    One way:

    Row\Col
    A
    B
    C
    D
    E
    F
    G
    1
    S.No
    Card Number
    Gen Time
    Reader
    2
    1
    20611
    01/03/2011 05:49
    Office Access IN #1
    86:58
    F2: =SUMIF($D$2:$D$27, "*OUT*", $C$2:$C$27) - SUMIF($D$2:$D$27, "*IN*", $C$2:$C$27)
    3
    2
    20611
    01/03/2011 15:23
    Office Access OUT #2
    4
    3
    20611
    01/04/2011 05:55
    Office Access IN #1
    5
    4
    20611
    01/04/2011 10:37
    Office Access OUT #2
    6
    5
    20611
    01/04/2011 11:57
    Office Access IN #1
    7
    6
    20611
    01/04/2011 15:23
    Office Access OUT #2
    8
    7
    20611
    01/05/2011 05:50
    Office Access IN #1
    9
    8
    20611
    01/05/2011 15:22
    Office Access OUT #2
    10
    9
    20611
    01/06/2011 05:53
    Office Access IN #1
    11
    10
    20611
    01/06/2011 15:22
    Office Access OUT #2
    12
    11
    20611
    01/07/2011 05:52
    Office Access IN #1
    13
    12
    20611
    01/07/2011 11:54
    Office Access OUT #2
    Entia non sunt multiplicanda sine necessitate

  8. #8
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Time Calculation - What is the easiest way to calculate hours worked per day?

    I take it that there is more than one individual involved here. Maybe this extension to shg's formula would be of use:

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    1
    S.No Card Number Gen Time Reader Card # Time
    2
    1
    20611
    01/03/2011 5:49
    Office Access IN #1
    20611
    52:32
    =SUMIFS($C$2:$C$27,$D$2:$D$27,"*"&"OUT"&"*",$B$2:$B$27,H2)-SUMIFS($C$2:$C$27,$D$2:$D$27,"*"&"IN"&"*",$B$2:$B$27,H2)
    3
    2
    20611
    01/03/2011 15:23
    Office Access OUT #2
    20612
    9:29
    4
    3
    20611
    01/04/2011 5:55
    Office Access IN #1
    20613
    9:13
    5
    4
    20611
    01/04/2011 10:37
    Office Access OUT #2
    6
    5
    20611
    01/04/2011 11:57
    Office Access IN #1
    7
    6
    20611
    01/04/2011 15:23
    Office Access OUT #2
    8
    7
    20611
    01/05/2011 5:50
    Office Access IN #1 Total
    71:14
    =SUM(I2:I7)
    9
    8
    20611
    01/05/2011 15:22
    Office Access OUT #2
    10
    9
    20612
    01/06/2011 5:53
    Office Access IN #1
    11
    10
    20612
    01/06/2011 15:22
    Office Access OUT #2
    12
    11
    20611
    01/07/2011 5:52
    Office Access IN #1
    13
    12
    20611
    01/07/2011 11:54
    Office Access OUT #2
    14
    13
    20611
    01/10/2011 5:53
    Office Access IN #1
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

+ 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. Replies: 4
    Last Post: 02-12-2013, 07:01 AM
  2. Replies: 2
    Last Post: 02-11-2013, 02:26 PM
  3. NEED help with Basic time sheet and hours worked calculation
    By ebyron in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-26-2012, 03:44 PM
  4. 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

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