help with excel needed

I believe that is possible but can you upload sample data rather than a screen shot?

We then have data to use rather than having to replicate it.

Thanks
Rob H

sample attached,
thanks for your good will.

Upload unsuccessful

sorry about that, now it should be ok.
total-hours.csv

Does true data come in this format, stumbling across slight issues with format of date and time; they are showing as text.

I can get round it for the sample but aware that this may be a bigger issue with full data set.

You might like to convert the data into a pivot table and then work on it. As robhenson mentioned you can convert the textual values to numeric values before it can be comprehended by the pivot table. See attached.
Total-hours.csv

robhenson - that's a real export from the software, i only changed the employee name to "employee".

Couple of anomalies in the data that you might like to clean up first.

8 Nov - Two out entries at 5:00 and 6:31, should 5:00 be "in" as went to lunch at 4:00pm
12 Nov - Two in entries at 9:31

With assumptions above and corrected, see attached.

The DATEVALUE formula should work correctly in your locale so shouldn't have to fudge it for different locales like I have to get the dates correct but if you look at the "Dec Hrs" & "Hrs Worked" columns on the UK tab it should give you an idea on how to do it. I have then made a pivot on the calculated data.

Thanks
Rob H
total-hours.xlsx

Rob, that's awesome, many thank for all your help!

The only change I'd really like to add to "UK" tab is highlight the time the employee was out at lunch or break, counting from lunch\break until the next in, break or lunch, that will start a new count so it also needs to stop a previous one (:

lunch can be bold blue and break bold orange just to distinguish between them.

Thanks again!

Apologies for delay, see attached.

Not sure I fully understand the additional requirement but have added a couple of columns to the data and the Pivot.

The Lunch and Break columns assume, maybe incorrectly, that the time stamp following the lunch/break stamp will be the same day. This is based on assumption if the employee is taking lunch/break they will be returning later the same day otherwise they would just clock out.

As a sanity check of the data, I have also added Arrival and Departure times. These can be easily removed from the Pivot by right clicking on the column header and choose Remove column from the popup menu.

Hope this helps.

Thanks
Rob H
total-hours.xlsx

Hi Rob, thanks for the great work,
Unfortunately I'm not sure how the latest excel file works,
For example, when I manually edit the break time on Nov 11th (under the UK tab), it doesn't change any field value other then the "break" column on the same line, but this field is in an unreadable format (full "time", e.g 00:00:00 am).

Can you upload your file with amendment so that I can see what you mean?

Thanks
Rob H

I just downloaded your latest attachment and tried to understand how I fill up new values (to calculate other employee's times).

I'm attaching to this message another excel file from our time clock, please help me understand how to replace the values on your excel with the ones I exported.

Again, many many thanks!

No attachment. Can you just attach the excel file with your Nov 11th amendment highlighted.

Thanks
Rob H

and as always i forgot to actually attach the file (:
example2.csv
total-hours.xlsx