Need Help with time sheet

[ridimo]

Hello there you intelligent bunch of cell crunching people.(please note the flattery )

So I have a new clocking machine for my employees and its a VERY basic system. So it exports a TAB delimited file which i manged to sort easily enough. What i then did was put it into a Pivot table in a format i would like it. the issue i have is that I need it to add the Total hours worked for each employee as a total for each day. the data looks like this

[TABLE="width: 500"]
[TR]
[TD]Clock number[/TD]
[TD]Machine No[/TD]
[TD]En No[/TD]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]AM/PM[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]Test[/TD]
[TD][TABLE="width: 111"]
[TR]
[TD="class: xl65, width: 111, align: right"]2015/08/21 00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE="width: 57"]
[TR]
[TD="class: xl65, width: 57, align: right"]07:39:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE="width: 68"]
[TR]
[TD="width: 68"]AM[/TD]
[/TR]
[/TABLE]
[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]Test[/TD]
[TD][TABLE="width: 111"]
[TR]
[TD="class: xl65, width: 111, align: right"]2015/08/21 00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE="width: 57"]
[TR]
[TD="class: xl65, width: 57, align: right"]09:58:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE="width: 68"]
[TR]
[TD="width: 68"]AM[/TD]
[/TR]
[/TABLE]
[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]Test[/TD]
[TD][TABLE="width: 111"]
[TR]
[TD="class: xl65, width: 111, align: right"]2015/08/21 00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE="width: 57"]
[TR]
[TD="class: xl65, width: 57, align: right"]10:13:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]Test[/TD]
[TD][TABLE="width: 111"]
[TR]
[TD="class: xl65, width: 111, align: right"]2015/08/21 00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE="width: 57"]
[TR]
[TD="class: xl65, width: 57, align: right"]01:01:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD]PM[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]Test[/TD]
[TD][TABLE="width: 111"]
[TR]
[TD="class: xl65, width: 111, align: right"]2015/08/21 00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE="width: 57"]
[TR]
[TD="class: xl65, width: 57, align: right"]01:29:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD]PM[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]Test[/TD]
[TD][TABLE="width: 111"]
[TR]
[TD="class: xl65, width: 111, align: right"]2015/08/21 00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE="width: 57"]
[TR]
[TD="class: xl65, width: 57, align: right"]05:29:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD]PM[/TD]
[/TR]
[TR]
[TD]41[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]Test[/TD]
[TD][TABLE="width: 111"]
[TR]
[TD="class: xl65, width: 111, align: right"]2015/08/24 00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE="width: 57"]
[TR]
[TD="class: xl65, width: 57, align: right"]07:03:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]Test[/TD]
[TD][TABLE="width: 111"]
[TR]
[TD="class: xl65, width: 111, align: right"]2015/08/24 00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE="width: 57"]
[TR]
[TD="class: xl65, width: 57, align: right"]10:06:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD]48[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]Test[/TD]
[TD][TABLE="width: 111"]
[TR]
[TD="class: xl65, width: 111, align: right"]2015/08/24 00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE="width: 57"]
[TR]
[TD="class: xl65, width: 57, align: right"]10:19:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD]49[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]Test[/TD]
[TD][TABLE="width: 111"]
[TR]
[TD="class: xl65, width: 111, align: right"]2015/08/24 00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE="width: 57"]
[TR]
[TD="class: xl65, width: 57, align: right"]12:58:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD]PM[/TD]
[/TR]
[TR]
[TD]52[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]Test[/TD]
[TD][TABLE="width: 111"]
[TR]
[TD="class: xl65, width: 111, align: right"]2015/08/24 00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE="width: 57"]
[TR]
[TD="class: xl65, width: 57, align: right"]01:28:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD]PM[/TD]
[/TR]
[TR]
[TD]57[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]Test[/TD]
[TD][TABLE="width: 111"]
[TR]
[TD="class: xl65, width: 111, align: right"]2015/08/24 00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE="width: 57"]
[TR]
[TD="class: xl65, width: 57, align: right"]05:37:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD]PM[/TD]
[/TR]
[/TABLE]


as you can see the employee clocks in on 10 then clocks out at 13 then back in at 16 and so on. How can I get it to total the amount of hours for each day. been racking my head on this but keep getting lost at the fact that there are so many in's and out's in 1 day.

any ideas would help

Thanks in advance.

 

[Acuity Training]

Hi,
Can you explain a bit more please?
Its easy enough to sum the hours for a day. Is the issue that you want to completely automate so that the pivot table produces the total hours?

 

[ridimo]

Well waht i want to do is create a "template" where dumping the raw data as above it will then update total hours for the day

 

[Acuity Training]

I think that the best way to do this is probably create a calculated field in your pivot table. Could you post the raw data in a spreadsheet on dropbox so that I can recreate the pivottable and hopefully find the solution.

 

[ridimo]

Sure send me PM