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.
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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
 

APreston

New Member
Joined
May 8, 2016
Messages
4

ADVERTISEMENT

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.
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
When the other sites have provided a workable solution, would you be so good to post a copy here
 

APreston

New Member
Joined
May 8, 2016
Messages
4

ADVERTISEMENT

Sure. I'd be glad to. :)
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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
 

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,490
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').
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,118,994
Messages
5,575,426
Members
412,662
Latest member
joelgibney1
Top