Results 1 to 8 of 8

Thread: Very complicated calculation of total hours worked (PLEASE HELP)

  1. #1

    Very complicated calculation of total hours worked (PLEASE HELP)



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi. I have a spreadsheet that shows the amount of time that somebody worked over the course of several years. The spreadsheet is about 30,000 rows long and very poorly organized (more on this below). I need to calculate the hours worked by this individual each day over the course of all the years shown on the spreadsheet, but there are NOT separate columns for time clocked in and time clocked out. If there were separate columns for those values, I could calculate the total hours worked on the first day/row, and then I could drag the formula down the entire spreadsheet.

    Unfortunately, as I said, there are not separate columns for the clock-in and clock-out times. Instead there is one column for both times. To make matters worse, the column that shows times does not only show time clocked in and time clocked out; it also shows the times at which various work tasks were performed throughout the day (between the clock-in and clock-out times).

    Anyway, my boss wants me to figure out the total number of work hours recorded on the spreadsheet, and I have no idea how to proceed. The column that shows times (clocked in, clocked out, etc.) is right next to a column that shows the date (which is entered as a number rather than a date). Also, the date is written in the format YYYYMMDD, just to make things more confusing. So this is what the spreadsheet looks like:

    DATE | TIME | ACTIVITY
    20080507 | 9:30 | clock-in/work task #1
    20080507 | 11:30 | work task #2
    20080507 | 2:30 | work task #3
    20080507 | 4:30 | clock-out
    20080508 | 9:30 | clock-in/work task #1
    20080508 | 11:30 | work task #2
    20080508 | 2:30 | work task #3
    20080508 | 4:30 | clock-out
    20080509 | 9:30 | clock-in/work task #1
    20080509 | 11:30 | work task #2
    20080509 | 2:30 | work task #3
    20080509 | 4:30 | clock-out

    (NOTE: The actual spreadsheet contains extreme variation between the days in terms of the hours worked on each day.)

    Anyway, given the strange format of this spreadsheet, I have no idea how to automatically calculate the total hours worked down the entire spreadsheet. The only thing that I can do is create formulas for each day (one day at a time) and then add up all the hours once I've calculated them day-by-day. The process would take months, which is too long for the demands of my boss. By the way, I have to do this for 7 other spreadsheets that are also about 30,000 rows long.

    Is there any way to create formulas that can 1) divide the rows into segments based on the DATE values 2) automatically calculate the total time elapsed within each segment/day (using the first and last time values for each segment/day, which would represent, respectively, time clocked-in and time clocked-out)?

    As you can see, this problem far exceeds my Excel capabilities. I truly appreciate any and all help that you guys can offer. Thank you.

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,843
    Articles
    0
    Excel Version
    365
    This shouldn't be difficult but could be fiddly if the data isn't consistent (can we say for certain that every clock-in and clock-out could be Auto-filtered for by looking for the presence of 'clock' in the ACTIVITY column?).
    If you could attach a sheet of real data (redacted if there's sensitive data) or a link to such a sheet (your current low post count may mean you can't post links or attach files, I'm not sure, but if you miss off the http:// part of a link the board software shouldn't complain) then I'd be willing to provide a solution.

    One question: those times, are they using a 24 hour clock or do we have to make some assumptions such as all times are between 9am and 5pm?
    Last edited by p45cal; 2016-05-08 at 05:15 PM.

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,843
    Articles
    0
    Excel Version
    365
    Oh groan, cross posted without links:

    http://www.excelforum.com/excel-form...ease-help.html
    http://www.msofficeforums.com/excel/...ed-please.html
    http://www.excelforum.com/excel-form...ease-help.html

    APreston, for your information (though you'll know it by now!), you should always provide links to your cross posts.
    If you have cross posted at other places, please add links to them too.
    Why? Have a read of http://www.excelguru.ca/content.php?184
    Last edited by p45cal; 2016-05-08 at 07:09 PM.

  4. #4
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,735
    Articles
    0
    Excel Version
    2010 on Xubuntu
    And probably aware of this because OP posts under another name : PrestonT
    Thank you Ken for this secure forum.

  5. #5
    I apologize for violating the cross-posting policy. I should have read the rules before posting; I was in a rush because the problem is work-related, but I promise that it won't happen again. (Just for clarification's sake, the reason that I posted under the name "PrestonT" elsewhere was that "APreston" was unavailable on those sites.) Thank you for not locking the thread.

    p45cal, thank you for your help. Yes, the time values are in 24-hour/military format. Unfortunately, the ACTIVITY cells don't actually say "clock-in" and "clock-out." They usually just include the first and last work tasks of the day, which are totally inconsistent. Therefore, there aren't really terms in that column that can help in automating the process. I'm sorry; I should have made it clear in my original post that those words aren't actually in the cells.

    I have attached the spreadsheet with all columns removed except for date and time. (The other columns contain sensitive information; plus, they don't contain any consistent terms that would help solve the problem.) I only included rows 1-15,000 because the original spreadsheet exceeded the file size limit for this forum.

    Thank you so much for any assistance. I truly appreciate it. :-)
    Attached Files Attached Files

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,843
    Articles
    0
    Excel Version
    365
    With just a series of date/time stamps it is impossible to calculate total time worked. There is no way to tell whether a given row is a continuation of working time (change of task) or a clock in or clock out.
    If data in other columns is inconsistent but there is still useful info I can still have a stab, but the current file simply doesn't have the data - unless you can tell me that adjacent entries are in then out, but I doubt it.
    BTW, I see latest time is around 10pm and earliest about 9am, can we assume no-one is working over midnight?
    Actually, just a thought, can we assume the earliest timestamp of a given day is clock in and the latest timestamp is clockout? So all the others can be ignored? In which case, yes it can be automated. I'll wait for confirmation of this.
    Last edited by p45cal; 2016-05-08 at 11:02 PM.

  7. #7
    I'm fairly certain that this person never worked later than midnight.

    Also, yes, my boss led me to believe that the earliest timestamp can be considered the clock-in and the latest one can be considered the clock-out. I think that the actual clock-out times occurred slightly later, but they obviously weren't recorded. Therefore, the last time stamp for each day is the closest approximation of a clock-out.

    Thank you once again.

  8. #8
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,735
    Articles
    0
    Excel Version
    2010 on Xubuntu
    OP has already posted at 7 different forums (and didn't care to add the three missing ones). Lots of people are losing time with this thread. Thread closed
    Thank you Ken for this secure forum.

Posting Permissions

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