Status
Not open for further replies.

#### APreston

##### New Member
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.

### 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
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
Excel Workbook
ABCDE
1DATETIMEACTIVITY
507/05/20084:30clock-out 4:30
908/05/20084:30clock-out 4:30
1309/05/20084:30clock-out 4:30
Sheet1

#### APreston

##### New Member

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
When the other sites have provided a workable solution, would you be so good to post a copy here

#### mole999

##### Moderator
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

#### APreston

##### New Member
I see. Thanks for the tip.

#### Macropod

##### Retired Moderator
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.

Replies
76
Views
2K
Replies
18
Views
378
Replies
1
Views
125
Replies
4
Views
215
Replies
7
Views
348