Calculating Times

[jmck]

Hello folks, I've performed a search for an answer to this but can't seem to find what I need.

I need to calculate hours worked for each shift and then have a grand total for each employee for all the shifts they have worked.

I thought I had a calculation for the individual shifts, however there is a slight problem with it - if the employee works through midnight on a night-shift then the calulation doesn't work.

And I just cannot work out how to total the daily totals into a grand total! Can anybody help?

I have attached a sample of what I am working with.

[teylyn]

Hi,

there seems to be some inconsistency with your data. How come people have two consecutive entries, both clocking out? E.g. rows 13, 44, 57, 68, and more. Some seem to be just double bookings, but some seem to be different dates, with no clocking in to pair with the clocking out time.

Also, there are cases with people clocking in twice in a row.

It will be very difficult do develop a formula based on that kind of data.

If your values were truly alternating between In and Out (as they logically should be), then you can simply add the date and time portion of each row, then subtract the Out row from the In row to arrive at the net time, which can be summed up.

=IF(RIGHT(C3,3)="out",(B3+D3)-(B2+D2),"")

cheers

[jmck]

Its because they have swiped their card twice in quick succession - the data is pulled out of a door security system. I have cleaned it up now and uploaded it again.

[teylyn]

try the formula I posted above in E2 and copy down. Format the result with custom format

[hh]:mm

After that, sum by name.

[jmck]

Thanks teylyn. The formula for calculating each shift works perfectly, however when I subtotal them, it fails. For example if you take the first person Adrian Taylor, he has worked the following hours

9.55
10.14
10.30
8.39

Which is a total of 38.38, when I subtotal that lot and format to hh:mm I get a total of 15:18.

[teylyn]

as I said above, you need to use the custom format

[hh]:mm

This will show hours greater than 24.

The 15:18 is really 1 day, 15 hrs, 18 minutes, where 1 day = 24 hrs. The cell format is just not showing the day. With the [brackets] you force Excel to total the hours even if more than 24 or 1 day.

cheers

[jmck]

Oops sorry didn't see the bracket around the hh!

[teylyn]

Please don't quote whole posts. It's just clutter.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save