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]

raw

06:11​

09:29​

08:13​

08:52​

08:46​

41:31​

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:

	C​	D​	E​	F​	G​	H​
1​	Weekly No.	7
2​	Driver	8 - Test
3​
4​		Values
5​	Day	Min of Start time	Max of End time	Active Time
6​	Mon 13/05​	07:42​	17:10​	09:28​	F6:​	=IFERROR(GETPIVOTDATA("Max of End time",$C$4,"Day",C6)-GETPIVOTDATA("Min of Start time",$C$4,"Day",C6),"- -")
7​	Tue 14/05​	06:46​	16:22​	09:36​
8​	Wed 15/05​	05:46​	14:10​	08:24​
9​	Thu 16/05​	06:41​	15:56​	09:15​
10​	Fri 17/05​	06:51​	15:51​	09:00​
11​				- -
12​				- -
13​				- -

maybe someone else will give you better solution