Your hours in column I of Sheet1 are text, not numbers. They need converting to numbers. You can do this by putting a formula in cell L3:
=IFERROR(TIMEVALUE(I3),0)
and copy it down as far as needed.
Then you can copy the new column L values, and paste them back into column I using Paste Special, Values. Then format them with the custom format: [h]:mm
You can delete column L now, although in the attached I've left it in for your information.
Now refresh your pivot.
You will also need to number format the Sum of Hours field in the same way:
Thank you. But, i am still not able to it myself. The Column I is difference of Column E and G (Time in and time out). It is cumbersome to add an addition dummy column, apply formula, copy the value to column I and then delete the newly created column etc. Cant avoid that?
I was quite surprised that Excel managed to correctly interpret strings to do arithmetic on.
Your formula does some interesting things where your original Hours column (I) had '-' (where the Time Out column was showing '00:00'). Some people are in for more than 23 hours.
Perhaps
=MAX(G3-E3,0)
might be of use?
aksalim
How that 'system' handle, if misses eg to do 'Logout'?
Those 00:00 would be 'automatic Logout's.
It would be more useful, if both, TimeIn and TimeOut, has also date-information.
... as well as, note, if the 'system' makes something.