Very complicated calculation of total hours worked (PLEASE HELP)

Status
Not open for further replies.

APreston

New Member
Joined
May 8, 2016
Messages
4
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. :eek:

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.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
I'm sure someone has a formula route.
simplified segments would be in an associated column test for clock in and if found place the time, in another column, test for clock out, the should give by day the in and out, I don't know how to turn that into a formula, but you could then select and paste the in and out times into a new sheet, then on the next sheet you could delete the top cell which would move up all your clock out times, delete all the blank rows and you have a date and in and out on the same line, take the clock in from clock out and formatted as hh:mm you get the total hours worked. Its a way, not very scientific
 
Upvote 0
Excel Workbook
ABCDE
1DATETIMEACTIVITY
207/05/20089:30clock-in/work task #19:30 
307/05/200811:30work task #2  
407/05/20082:30work task #3  
507/05/20084:30clock-out 4:30
608/05/20089:30clock-in/work task #19:30 
708/05/200811:30work task #2  
808/05/20082:30work task #3  
908/05/20084:30clock-out 4:30
1009/05/20089:30clock-in/work task #19:30 
1109/05/200811:30work task #2  
1209/05/20082:30work task #3  
1309/05/20084:30clock-out 4:30
Sheet1
 
Upvote 0
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. Thank you for not locking the thread.

Mole, thank you so much for your suggestions. There is one problem: 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. Therefore, those terms can't be used to help automate the process. I'm sorry; I should have made it clear in my original post that those words aren't actually in the cells.
 
Upvote 0
When the other sites have provided a workable solution, would you be so good to post a copy here
 
Upvote 0
something to investigate, with the available date and time, combining them will give you values for earliest and latest for the day, so a Min and Max of those should develop into what you are after
 
Upvote 0
In C2, input:
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))+(INT(B2/100)+MOD(B2,100)/60)/24
In D2, input:
=IF(A2=A1,"",OFFSET(C2,COUNTIF(A:A,A2)-1,0)-C2)
Copy both columns down as far as the data go. Column D will report the #hrs clocked on each day.
In E2, input:
=SUM(D:D)
for the total #hrs in column D.

If you want, you can format column C to show dates & times (e.g. with a custom format like 'dd/mm/yyyy hh:mm'). Similarly, you can format column D & E2 to show hours & minutes (e.g. 'hh:mm').
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top