Calculate Hours&Mins in Pivot Table

[National_Shipping]

So, I want to create a Pivot table that displays DELIVERY DATE, sum of RUN#, sum of ORDER#, sum of ORDER AMOUNT, calculates RUN TIME (using DISPATCH TIME & RETURN TIME) in one or two Pivot Table(s). I am having a tough time figuring it out.

Data sample is below that I import into a table from another excel file. So...

Scheduled Asset	Order #	Invoice #	Delivery Date	Order Amount	Dispatch Time	Run#	Return Time
b T12	939387	939387	3/1/2019	2416.47	5:59:00 AM	3728	3:03:00 PM
b T12	939032	J39032	3/1/2019	920.38	5:59:00 AM	3728	3:03:00 PM
b T12	935695	851740	3/1/2019	0	5:59:00 AM	3728	3:03:00 PM
b T12	942040	942040	3/1/2019	646.05	5:59:00 AM	3728	3:03:00 PM
b T12	944240	944240	3/1/2019	893.1	5:59:00 AM	3728	3:03:00 PM
b T12	941981	941981	3/1/2019	317.94	5:59:00 AM	3728	3:03:00 PM
b T12	942344	942344	3/1/2019	891.95	5:59:00 AM	3728	3:03:00 PM
b T12	938106	K38106	3/1/2019	414.95	5:59:00 AM	3728	3:03:00 PM
b T12	944048	944048	3/1/2019	112.35	5:59:00 AM	3728	3:03:00 PM
b T12	942994	942994	3/1/2019	336.5	5:59:00 AM	3728	3:03:00 PM
b T12	944232	851745	3/1/2019	0	5:59:00 AM	3728	3:03:00 PM
f T5	944331	944331	3/1/2019	115.69	6:43:00 AM	3740	8:33:00 AM
f T5	943915	943915	3/1/2019	608.16	6:43:00 AM	3740	8:33:00 AM
m T14	943407	J43407	3/1/2019	2027.04	6:50:00 AM	3739	8:25:00 AM
m T14	944204	944204	3/1/2019	472.5	6:50:00 AM	3739	8:25:00 AM
b T13	943479	J43479	3/1/2019	1488.82	6:57:00 AM	3730	11:03:00 AM
b T13	939219	939219	3/1/2019	493.47	6:57:00 AM	3730	11:03:00 AM
b T13	941287	941287	3/1/2019	306.33	6:57:00 AM	3730	11:03:00 AM
b T13	939608	939608	3/1/2019	2117.76	6:57:00 AM	3730	11:03:00 AM
b T13	942643	942643	3/1/2019	198.43	6:57:00 AM	3730	11:03:00 AM
b T15	942233	942233	3/1/2019	988.75	7:16:00 AM	3726	11:00:00 AM
b T15	943766	943766	3/1/2019	102.77	7:16:00 AM	3726	11:00:00 AM
b T15	936471	936471	3/1/2019	2148.31	7:16:00 AM	3726	11:00:00 AM
b T15	936495	936495	3/1/2019	2148.31	7:16:00 AM	3726	11:00:00 AM
b T15	936564	936564	3/1/2019	2097.7	7:16:00 AM	3726	11:00:00 AM
b T11	899291	I99291	3/1/2019	11416	7:19:00 AM	3729	11:36:00 AM
b T11	941893	941893	3/1/2019	277.18	7:19:00 AM	3729	11:36:00 AM
b T11	942165	942165	3/1/2019	268.71	7:19:00 AM	3729	11:36:00 AM
b T11	925239	925239	3/1/2019	853.3	7:19:00 AM	3729	11:36:00 AM
b T11	939620	851741	3/1/2019	0	7:19:00 AM	3729	11:36:00 AM
m T10	944194	944194	3/1/2019	10276.05	7:55:00 AM	3738	10:00:00 AM

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[sandy666]

Re: Calculate Hours&Mins in Pivot Table - HELP

like this?

Scheduled Asset	Delivery Date	Sum of Run#	Count of Order #	Sum of Order Amount	Run Time
b T11	03/01/2019​	18645​	5​	12815.19​	21:25:00​
b T12	03/01/2019​	41008​	11​	6949.69​	3:44:00​
b T13	03/01/2019​	18650​	5​	4604.81​	20:30:00​
b T15	03/01/2019​	18630​	5​	7485.84​	18:40:00​
f T5	03/01/2019​	7480​	2​	723.85​	3:40:00​
m T10	03/01/2019​	3738​	1​	10276.05​	2:05:00​
m T14	03/01/2019​	7478​	2​	2499.54​	3:10:00​

 

[National_Shipping]

Re: Calculate Hours&Mins in Pivot Table - HELP

like this?

Scheduled Asset	Delivery Date	Sum of Run#	Count of Order #	Sum of Order Amount	Run Time
b T11	03/01/2019​	18645​	5​	12815.19​	21:25:00​
b T12	03/01/2019​	41008​	11​	6949.69​	3:44:00​
b T13	03/01/2019​	18650​	5​	4604.81​	20:30:00​
b T15	03/01/2019​	18630​	5​	7485.84​	18:40:00​
f T5	03/01/2019​	7480​	2​	723.85​	3:40:00​
m T10	03/01/2019​	3738​	1​	10276.05​	2:05:00​
m T14	03/01/2019​	7478​	2​	2499.54​	3:10:00​

<tbody>
</tbody>

Thanks for the response. Not quite here is an example on how far I have gotten. I cant figure out the calc of the Run Time length.

Delivery Date	Scheduled Asset	Run#	Count of Order #	Sum of Order Amount	Sum of Dispatch Time	Sum of Return Time	Sum of Run TimeC
3/1/2019	b T11	3729	5	$12,815.19	1.524305556	2.416666667	0
		3735	3	$4,856.19	1.577083333	1.833333333	0
	b T12	3728	11	$6,949.69	2.742361111	6.897916667	0
	b T13	3730	5	$4,604.81	1.447916667	2.302083333	0
		3737	2	$12,204.52	1.043055556	1.231944444	0
	b T15	3726	5	$7,485.84	1.513888889	2.291666667	0
		3736	5	$7,560.72	2.538194444	3.197916667	0
	f T4	3757	1	$0.00	0.633333333	0.670833333	0
	f T5	3740	2	$723.85	0.559722222	0.7125	0
		3742	3	$2,701.88	1.1875	1.3375	0
		3746	1	$564.77	0.589583333	0.633333333	0
		3749	1	$0.01	0.465972222	0.575	0
	m T10	3738	1	$10,276.05	0.329861111	0.416666667	0
		3744	2	$4,734.26	0.913888889	1.094444444	0
		3748	2	$1,573.21	1.165277778	1.318055556	0
	m T14	3739	2	$2,499.54	0.569444444	0.701388889	0
		3741	5	$5,545.69	2.034722222	2.739583333	0
3/1/2019 Total			56	$85,096.22	20.83611111	30.37083333	0
3/2/2019	f T4	3755	1	$1,625.40	0.373611111	0.427777778	0

<colgroup><col><col span="2"><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[sandy666]

Re: Calculate Hours&Mins in Pivot Table - HELP

calculated field: return - dispatch

Delivery Date	Scheduled Asset	Run#	Count of Order #	Sum of Order Amount	Run Time
03/01/2019​	b T11	3729​	5​	12815.19​	21:25:00​
	b T12	3728​	11​	6949.69​	3:44:00​
	b T13	3730​	5​	4604.81​	20:30:00​
	b T15	3726​	5​	7485.84​	18:40:00​
	f T5	3740​	2​	723.85​	3:40:00​
	m T10	3738​	1​	10276.05​	2:05:00​
	m T14	3739​	2​	2499.54​	3:10:00​
Grand Total			31​	45354.97​	1:14:00​

 

[sandy666]

Re: Calculate Hours&Mins in Pivot Table - HELP

ignore post above

Delivery Date	Scheduled Asset	Run#	Count of Order #	Sum of Order Amount	Run Time
03/01/2019​	b T11	3729​	5​	12815.19​	21:25:00​
	b T12	3728​	11​	6949.69​	99:44:00​
	b T13	3730​	5​	4604.81​	20:30:00​
	b T15	3726​	5​	7485.84​	18:40:00​
	f T5	3740​	2​	723.85​	3:40:00​
	m T10	3738​	1​	10276.05​	2:05:00​
	m T14	3739​	2​	2499.54​	3:10:00​
Grand Total			31​	45354.97​	169:14:00​