Time in pivot table issue

[perkinsr1]

Hi folks,
I hope you are all well? I am having an issue with my pivot table, I have times in my raw data and I want to work out what the % of the columns F to O against column D, When I try I have converted it to different formats but either get a 1 or 24:00 so not able to convert to a %.

I hope this makes sense and I have added a picture, any help would be great.

Thanks in advance

Rob

 

[GraH]

Hi, it's a bit difficult with pictures... Consider using the XL2BB add-on to post sample data.

Seems you are counting the Time Calc Management Time. the result 1 suggest there is only 1 occurrence per name per week. 24:00 is returned, because the field is a time.
Not clear what's the percentage you are after. If it is the % of the column total then you might need to use SUM and show result as % of Columns.

 

[perkinsr1]

Hiya,
Sorry, I have a few option in the data, the % columns I have worked out the % of time but it will not pull the answer through to the pivot table, the time calc was a suggestion but that hasn't worked, I have attached the data I am using.
I a simple way, I am after the percentage of time between columns F to O individually against the time in column D

So time spent in "Training" against "Planned HH:MM:SS, this will allow me to get their % of "productive time" I need

Hope this helps?

Adherence Trend v2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO
1	Week	Team Leader	Agent Name	Planned HH:MM:SS	Logon HH:MM:SS	Prod Aux HH:MM:SS	BackOffice HH:MM:SS	Ecomms HH:MM:SS	Live Chat HH:MM:SS	Outbound HH:MM:SS	Q & A HH:MM:SS	UnProd HH:MM:SS	Management Time HH:MM:SS	System Issues HH:MM:SS	Training HH:MM:SS	Offsite%	STA%	LTA%	ST Sick%	Holiday%	Prod Aux%	BackOffice%	Ecomms%	Live Chat%	Outbound%	Q & A%	UnProd%	Management Time%	System Issues%	Training%	Time calc Planned	Time Calc Prod Aux	Time Calc Back Office	Time Calc Ecomms	Time calc Live chat	Time Calc Outbound	Time Calc Q&A	Time Calac Un Prod	Time Calc Management Time	Time Calc System Issues	Time Calc Training
2	wk04	Melissa Flatley	Shawn Hodson	58:00:00	10:12:48	0:00:00	0:00:00	0:00:00	0:00:00	0:00:00	0:00:00	9:58:19	9:58:19	0:00:00	0:00:00	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	17.19%	17.19%	0.00%	0.00%	10:00:00	00:00:00	00:00:00	00:00:00	00:00:00	00:00:00	00:00:00	09:58:19	09:58:19	00:00:00	00:00:00
3	wk04	Wendy Jones	Louise Fillingham	21:00:00	17:47:38	3:50:35	3:50:35	0:00:00	0:00:00	0:00:00	0:00:00	13:25:25	8:48:42	0:00:00	4:36:43	9.50%	0.00%	0.00%	0.00%	9.50%	18.30%	18.30%	0.00%	0.00%	0.00%	0.00%	63.92%	41.96%	0.00%	21.96%	21:00:00	03:50:35	03:50:35	00:00:00	00:00:00	00:00:00	00:00:00	13:25:25	08:48:42	00:00:00	04:36:43
4	wk04	Jon Davies	Emma Baker	54:00:00	18:57:12	0:00:00	0:00:00	0:00:00	0:00:00	0:00:00	0:00:00	7:00:13	6:53:07	0:07:06	0:00:00	100.00%	95.40%	0.00%	0.00%	4.60%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	12.97%	12.75%	0.22%	0.00%	06:00:00	00:00:00	00:00:00	00:00:00	00:00:00	00:00:00	00:00:00	07:00:13	06:53:07	00:07:06	00:00:00
5	wk04	Ashley Clough	Charlie Hardy	49:00:00	7:24:37	0:00:00	0:00:00	0:00:00	0:00:00	0:00:00	0:00:00	6:23:44	6:23:44	0:00:00	0:00:00	85.70%	15.30%	0.00%	0.00%	70.40%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	13.05%	13.05%	0.00%	0.00%	01:00:00	00:00:00	00:00:00	00:00:00	00:00:00	00:00:00	00:00:00	06:23:44	06:23:44	00:00:00	00:00:00
6	wk04	Alison Barnes	Danielle Couzens	30:00:00	43:55:50	39:53:47	39:53:47	0:00:00	0:00:00	0:00:00	0:00:00	4:01:33	4:01:33	0:00:00	0:00:00	0.00%	0.00%	0.00%	0.00%	0.00%	132.99%	132.99%	0.00%	0.00%	0.00%	0.00%	13.42%	13.42%	0.00%	0.00%	06:00:00	15:53:47	15:53:47	00:00:00	00:00:00	00:00:00	00:00:00	04:01:33	04:01:33	00:00:00	00:00:00
7	wk04	Alison Barnes	Michelle Peers	35:00:00	37:04:51	0:00:00	0:00:00	0:00:00	0:00:00	0:00:00	0:00:00	36:55:27	36:55:27	0:00:00	0:00:00	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	105.50%	105.50%	0.00%	0.00%	11:00:00	00:00:00	00:00:00	00:00:00	00:00:00	00:00:00	00:00:00	12:55:27	12:55:27	00:00:00	00:00:00
8	wk04	Audra Timms	Carol Grace	35:00:00	34:34:41	0:00:00	0:00:00	0:00:00	0:00:00	0:00:00	0:00:00	5:57:15	3:51:08	2:06:07	0:00:00	7.10%	0.00%	0.00%	0.00%	7.10%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	17.01%	11.01%	6.01%	0.00%	11:00:00	00:00:00	00:00:00	00:00:00	00:00:00	00:00:00	00:00:00	05:57:15	03:51:08	02:06:07	00:00:00
9	wk04	Scott Butler	Christopher Bellis	35:00:00	37:14:04	0:00:12	0:00:00	0:00:00	0:00:00	0:00:12	0:00:00	3:38:30	3:36:33	0:01:57	0:00:00	0.00%	0.00%	0.00%	0.00%	0.00%	0.01%	0.00%	0.00%	0.00%	0.01%	0.00%	10.40%	10.31%	0.09%	0.00%	11:00:00	00:00:12	00:00:00	00:00:00	00:00:00	00:00:12	00:00:00	03:38:30	03:36:33	00:01:57	00:00:00
10	wk04	Rebecca Cairns	Sam Macfarlane	47:00:00	49:29:38	1:07:45	0:36:13	0:00:00	0:00:00	0:31:32	0:00:00	2:31:19	2:09:32	0:00:00	0:21:47	0.00%	0.00%	0.00%	0.00%	0.00%	2.40%	1.28%	0.00%	0.00%	1.12%	0.00%	5.37%	4.59%	0.00%	0.77%	23:00:00	01:07:45	00:36:13	00:00:00	00:00:00	00:31:32	00:00:00	02:31:19	02:09:32	00:00:00	00:21:47
11	wk04	Alison Barnes	Heather Grant	25:00:00	6:10:48	0:00:00	0:00:00	0:00:00	0:00:00	0:00:00	0:00:00	5:52:21	2:52:23	0:00:00	2:59:58	64.00%	24.00%	0.00%	0.00%	40.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	23.49%	11.49%	0.00%	12.00%	01:00:00	00:00:00	00:00:00	00:00:00	00:00:00	00:00:00	00:00:00	05:52:21	02:52:23	00:00:00	02:59:58
12	wk04	Scott Butler	Ben Catton	24:00:00	24:13:00	0:00:00	0:00:00	0:00:00	0:00:00	0:00:00	0:00:00	2:39:54	2:39:54	0:00:00	0:00:00	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	11.10%	11.10%	0.00%	0.00%	00:00:00	00:00:00	00:00:00	00:00:00	00:00:00	00:00:00	00:00:00	02:39:54	02:39:54	00:00:00	00:00:00
13	wk04	Chris Carney	Ann Barwise	26:00:00	10:11:39	0:00:00	0:00:00	0:00:00	0:00:00	0:00:00	0:00:00	2:41:53	2:37:23	0:04:30	0:00:00	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	10.38%	10.09%	0.29%	0.00%	02:00:00	00:00:00	00:00:00	00:00:00	00:00:00	00:00:00	00:00:00	02:41:53	02:37:23	00:04:30	00:00:00
14	wk04	Ben Ludgate	Lynn Lightfoot	23:00:00	5:06:20	1:38:37	1:38:37	0:00:00	0:00:00	0:00:00	0:00:00	2:37:22	2:37:22	0:00:00	0:00:00	0.00%	0.00%	0.00%	0.00%	0.00%	7.15%	7.15%	0.00%	0.00%	0.00%	0.00%	11.40%	11.40%	0.00%	0.00%	23:00:00	01:38:37	01:38:37	00:00:00	00:00:00	00:00:00	00:00:00	02:37:22	02:37:22	00:00:00	00:00:00
15	wk04	Angie Noscoe	Jessica O'Brien	24:00:00	17:37:24	0:11:00	0:00:00	0:00:00	0:00:00	0:11:00	0:00:00	2:38:57	2:34:10	0:04:47	0:00:00	25.00%	0.00%	0.00%	25.00%	0.00%	0.76%	0.00%	0.00%	0.00%	0.76%	0.00%	11.04%	10.71%	0.33%	0.00%	00:00:00	00:11:00	00:00:00	00:00:00	00:00:00	00:11:00	00:00:00	02:38:57	02:34:10	00:04:47	00:00:00
16	wk04	Angie Noscoe	Jacob Samuel	24:00:00	15:26:42	0:00:00	0:00:00	0:00:00	0:00:00	0:00:00	0:00:00	2:26:33	2:26:33	0:00:00	0:00:00	33.30%	0.00%	0.00%	0.00%	33.30%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	10.18%	10.18%	0.00%	0.00%	00:00:00	00:00:00	00:00:00	00:00:00	00:00:00	00:00:00	00:00:00	02:26:33	02:26:33	00:00:00	00:00:00
17	wk04	Sue Caine	Elaine Quirk	35:00:00	37:06:21	28:44:13	28:44:13	0:00:00	0:00:00	0:00:00	0:00:00	2:23:41	2:23:41	0:00:00	0:00:00	0.00%	0.00%	0.00%	0.00%	0.00%	82.11%	82.11%	0.00%	0.00%	0.00%	0.00%	6.84%	6.84%	0.00%	0.00%	11:00:00	04:44:13	04:44:13	00:00:00	00:00:00	00:00:00	00:00:00	02:23:41	02:23:41	00:00:00	00:00:00
18	wk04	Alex Carroll	James Darville	27:00:00	31:37:45	22:14:25	0:00:00	0:00:00	0:00:00	0:00:00	0:00:00	3:25:36	2:18:13	0:00:00	1:07:23	0.00%	0.00%	0.00%	0.00%	0.00%	82.37%	0.00%	0.00%	0.00%	0.00%	0.00%	12.69%	8.53%	0.00%	4.16%	03:00:00	22:14:25	00:00:00	00:00:00	00:00:00	00:00:00	00:00:00	03:25:36	02:18:13	00:00:00	01:07:23
19	wk04	Sarah Davies	Estelle King	30:30:00	23:40:02	0:52:43	0:00:00	0:00:00	0:00:00	0:52:43	0:00:00	2:11:20	2:11:18	0:00:02	0:00:00	26.20%	26.20%	0.00%	0.00%	0.00%	2.88%	0.00%	0.00%	0.00%	2.88%	0.00%	7.18%	7.17%	0.00%	0.00%	06:30:00	00:52:43	00:00:00	00:00:00	00:00:00	00:52:43	00:00:00	02:11:20	02:11:18	00:00:02	00:00:00
20	wk04	Hannah Lister	Karen Davies	26:30:00	29:31:59	0:00:00	0:00:00	0:00:00	0:00:00	0:00:00	0:00:00	24:19:05	11:50:20	0:00:00	12:28:45	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	91.77%	44.68%	0.00%	47.09%	02:30:00	00:00:00	00:00:00	00:00:00	00:00:00	00:00:00	00:00:00	00:19:05	11:50:20	00:00:00	12:28:45
Shrinkage Raw Data

Cell Formulas
Range	Formula
U2:U20	U2	=([@[Prod Aux HH:MM:SS]]/[@[Planned HH:MM:SS]])
V2:V20	V2	=([@[BackOffice HH:MM:SS]]/[@[Planned HH:MM:SS]])
W2:W20	W2	=([@[Ecomms HH:MM:SS]]/[@[Planned HH:MM:SS]])
X2:X20	X2	=([@[Live Chat HH:MM:SS]]/[@[Planned HH:MM:SS]])
Y2:Y20	Y2	=([@[Outbound HH:MM:SS]]/[@[Planned HH:MM:SS]])
Z2:Z20	Z2	=([@[Q & A HH:MM:SS]]/[@[Planned HH:MM:SS]])
AA2:AA20	AA2	=([@[UnProd HH:MM:SS]]/[@[Planned HH:MM:SS]])
AB2:AB20	AB2	=([@[Management Time HH:MM:SS]]/[@[Planned HH:MM:SS]])
AC2:AC20	AC2	=([@[System Issues HH:MM:SS]]/[@[Planned HH:MM:SS]])
AD2:AD20	AD2	=([@[Training HH:MM:SS]]/[@[Planned HH:MM:SS]])
AE2:AE20	AE2	=D2
AF2:AO20	AF2	=F2
B2:B20	B2	=INDEX(VLookup!$1:$1,SUMPRODUCT(MAX((VLookup!$A$1:$CH$17='Shrinkage Raw Data'!C2)*(COLUMN(VLookup!$A$1:$CH$17))))-COLUMN(VLookup!$1:$1)+1)

 

[GraH]

Hi thanks for the data, that really helps.

The way I understand (If I'm way off, I apologize) and see the data, is you have all those % in your data table calculated (columns U:AD?).
So why are you no using those fields in your pivot? Or are you calculating this per Team Lead, in that case those % are off?

 

[perkinsr1]

Hiya, thanks for the reply,
When I use the % data I get the below error:

xl2bb.xlam
	C	D
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sheet1