Adding times together for totals

[richiebkerry]

Hello, any help would be greatly appreciated.

I am trying to add together 5 days worth of time data to give a total.

For example the data is (E10) 06:11, (E11) 09:29, (E12) 08:13, (E13) 08:52, (E14) 08:46 to total 41:31 and not 17:31

 

[sandy666]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]raw[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

06:11​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

09:29​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

08:13​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

08:52​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

08:46​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00]

41:31​

[/td][/tr]
[/table]

change format to [hh]:mm then sum

 

[richiebkerry]

Thanks, great help.

 

[richiebkerry]

Thanks Sandy666, I have created a pivot table and inserted a calculated field to automatically calculate the difference between the 'End time'-'Start time'... it should give an answer of 09:28 (17:10-07:42) but gives 06:11... I have tried the above solution of formatting the calculated cell [hh]:mm but still don't have the correct answer of 09:28. Any help would be great.

 

[sandy666]

try select time column/range then custom format as above

or

post a link to shared excel file with your pivot table (OneDrive, GoogleDrive, DropBox.... or any similar)

 

[richiebkerry]

Thank you

https://www.dropbox.com/s/rytt2ktjwmt7ncv/1st Start - Last Finish 290519.xls?dl=0

 

[sandy666]

I realized this is format for Excel 2003 or less

So....
Correct your source range with dates, now there is a text not a real dates
if you fix it your PivotTable will work correctly

 

[sandy666]

your source data should have dates like this

 

[sandy666]

I tried it more times and for XL2003 conclusion is:

[Table="width:, class:head"][tr=bgcolor:#000000][th] [/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

1​

[/td][td]Weekly No.[/td][td]7[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

2​

[/td][td]Driver[/td][td]8 - Test[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

4​

[/td][td][/td][td]Values[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

5​

[/td][td]Day[/td][td]Min of Start time[/td][td]Max of End time[/td][td]Active Time[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

6​

[/td][td]

Mon 13/05​

[/td][td]

07:42​

[/td][td]

17:10​

[/td][td]

09:28​

[/td][td]

F6:​

[/td][td]=IFERROR(GETPIVOTDATA("Max of End time",$C$4,"Day",C6)-GETPIVOTDATA("Min of Start time",$C$4,"Day",C6),"- -")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

7​

[/td][td]

Tue 14/05​

[/td][td]

06:46​

[/td][td]

16:22​

[/td][td]

09:36​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

8​

[/td][td]

Wed 15/05​

[/td][td]

05:46​

[/td][td]

14:10​

[/td][td]

08:24​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

9​

[/td][td]

Thu 16/05​

[/td][td]

06:41​

[/td][td]

15:56​

[/td][td]

09:15​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

10​

[/td][td]

Fri 17/05​

[/td][td]

06:51​

[/td][td]

15:51​

[/td][td]

09:00​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

11​

[/td][td][/td][td][/td][td][/td][td]- -[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

12​

[/td][td][/td][td][/td][td][/td][td]- -[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

13​

[/td][td][/td][td][/td][td][/td][td]- -[/td][td][/td][td][/td][/tr]
[/table]

maybe someone else will give you better solution