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

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. 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?

3. 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

4. And probably aware of this because OP posts under another name : PrestonT

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. :-)

6. 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.

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. 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

#### Posting Permissions

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