Very complicated calculation of total hours worked (PLEASE HELP)

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.

I'm very sorry for violating the cross-posting policy. It wasn't unintentional and won't happen again.

John, thank you so much. I'll try out your solution.

Hi, John. I just had the chance to take a closer look at the program you created. I'm a little confused. How can I paste all of my data into column A, given that there are multiple columns in play here (specifically, the date and time columns)?

Also, I'm sorry. The third column (ACTIVITY) doesn't actually contain the terms "clock-in" and "clock-out." I should have made that clear above. The stuff entered in that column is totally inconsistent; it doesn't contain anything that can be used to help filter the cells. Is there another way to automate the overall calculation?

Again, thank you so much for your time and generosity.

A senior moment on my part re the one column : working with 3 (or more) columns is fine.

However unless it is possible to identify the "Clock in"/"clock out" times we cannot filter: is the first entry for a date the clock-in time (and likewise the last entry the clock out time)?

Please post a file with a good sample of the data.

To upload file: click "Go advanced" then "Manage Attachments".

Yes, we can assume that the first time for each date is the clock-in time and the last time is the clock-out time.

Given the large size of the spreadsheet, I have attached the first half (rows 1-15,000). I removed the activity column, as it seems useless and it's technically sensitive information.

Thank you.

Please Login or Register  to view this content.

See attached

I left both date columns in as a check.

@Pepe ...... you could be right! I'll await OP's response if any.

Hi. John, I'm trying out your solution. Apologies for the delayed response. I really appreciate your time and generosity. Thank you.

Also, I'm sorry for not adding the additional links. I'll do so from now on if there's ever any need, but I'll try to avoid cross-posting altogether.

John,

This is super. Thank you.

I have to concede that I posted this question in too many forums, and that was discourteous.

If your problem has been resolved, could you please mark the thread as solved ("Thread Tools" at top of first post).

FYI: I did check some of the other forums and noted that you had (VBA) responses to your initial posting, illustrating why cross-posting should not been done as it can (does) result in wasted time and effort.

While I have no experience of other forums, my experience of this one is that you will get very quick responses to any query: so hopefully lesson learned.

I marked the thread as solved.

Yes, part of the reason that I cross-posted so excessively was that I didn't realize how eager people were to actually help. I assumed it would be more like, post on 5-10 message boards and be lucky if one of them yields a full solution. So, indeed, lesson learned.

Thanks again, boss.

Hi John,

I actually had a follow-up question. I PM'd you, but the message didn't appear in my "Sent" folder, so I replicated it below to be on the safe side.

How can I add more rows of data (in columns A and B) in such a way that the additional total hours calculations (shown in column J) are made correctly?

I just tried to add more rows of data to columns A and B. Then, I dragged down columns F through J one column at a time (by double-clicking on the black crosses). It seems that I botched the formulas in the process. This is what happened:

-The dates in F and H automatically increase by 1 after row 657. Thus, weekend dates that weren't included in the original data now appear.
-The hours in columns G and I always increase increase by 1 after row 657.
-As a result, column J remains static after row 657, repeatedly showing a value of 6:17.

Any help is greatly appreciated.

Separate question: Is there a way for me to delete all of the data that I have in A and B, insert new data, and get the correct calculations in column J? The reason that I ask is that I have to do this again for 7 additional spreadsheets full of data.

Sorry to bother you again. Thanks so much for your time.

Best,
Preston

As per your PM: see attached file.

Thank you kindly

If your problem has been addressed, can you please mark thread as solved.

Done. Thanks.

See attached:

See attached: clears 100000 rows and added code to calculate time rather than use formula.

Please Login or Register  to view this content.

See Attached: I had problems adding to previous note.