Calculating Time in Pivot

[Maleficent]

Hi all,

I have a list of data which includes names, tasks and duration of time spent on task.

Eg.

Joe Bloggs - Task 1 - 2 hrs
Joe Bloggs - Task 1 - 1.5 hrs
Joe Bloggs - Task 2 - 3 hrs
Adam Smith - Task 1 - 1 hr
Adam Smith - Task 2 - 3 hrs
Adam Smith - Task 2 - 4 hrs
etc

There are multiple entries for each person and task as they carry out each task daily and I am looking at the month

What I need to do is summarise the total time spent on each task by each person in the month

I thought the best option was a pivot table but I'm having trouble summing the time

I have tried formatting the duration column both before and after creating the pivot but can't get it to work

Incidentally, in list format, if I add the durations eg. A1+B1 I get the correct answer. But if I try and sum eg. Sum(A1,B1) I just get 00:00:00?

I have tried formatting with standard time and [HH]:mm

any ideas? I know this should be a simple task and I'm probably missing something really obvious!!

[Pepe Le Mokko]

You probably have text looking like time somewhere
Please post a sample sheet with some data and expected results

[Maleficent]

Thanks, sample attached

[Pepe Le Mokko]

Just select col D - Data tab - Text to columns - Click Finish(nothing else required) - All contents should have shifted right
Now try your PT again

[Maleficent]

Thank you Pepe!! no idea how or why that worked but you've saved me tearing any more hair out!

[Pepe Le Mokko]

just watch out in the future : Left aligned = Text - Right aligned = number ( date and time are numbers to excel but shown in time/date format)
and of course where no manual alignment is applied !