pivot table help

[fastballfreddy]

I have 2 pivot tables that I'd like to be able to join in a certain view and not sure if it can be done or not. Looking for suggestions and maybe a pivot table isn't the best option so open to ideas.

I want to see Hour and Time (avaliable) on top with Jobs on the left side. Adding xl2bb of 2 pivot tables, data for the pivot table and format I'm looking to obtain (open to other ideas that may work as well).


Currently I have 2 pivot tables. One for the jobs done and the other for time.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
3	Count of Job	_
4	Row Labels	5	6	7	8	9	10	11	12	13	14	Grand Total
5	01/03/2023	7	7	8	7	8	7	8	7	7		66
6	Job 1	5		2								7
7	Job 2	2	2									4
8	Job 3		5	1								6
9	Job 4			4								4
10	Job 5			1	7	8	3					19
11	Job 6						4	8	7	7		26
12	(blank)
13	01/04/2023	17	20	20	20	13	10	10	10	10	8	138
14	Job 2								3			3
15	Job 5		13	20	8		3					44
16	Job 8				12	13	6					31
17	Job 9						1	10	7	10	8	36
18	Job3	17	7									24
19	(blank)
20	Grand Total	24	27	28	27	21	17	18	17	17	8	204
21
28
29	Sum of time	_
30	Row Labels	5	6	7	8	9	10	11	12	13	14	Grand Total
31	01/03/2023	0:55	1:00	1:00	0:45	1:00	0:30	1:00	0:45	0:30		7:25
32	01/04/2023		1:00	1:00	0:51	0:45	1:00	0:30	0:43	0:45	1:00	7:34
33	Grand Total	0:55	2:00	2:00	1:36	1:45	1:30	1:30	1:28	1:15	1:00	14:59
Sheet3

Here is the data for the pivot table although I wasn't able to show both dates.

Book2
	A	B	C	D	E	F	G	H	I	J
1	ID	Job	date stamp	name	Dept	time	hour 2	hour	month	date
2	1234	Job 1	1/3/2023 05:09:00 AM	Cristina				5	1	01/03/2023
3	1235	Job 1	1/3/2023 05:17:00 AM	Cristina				5	1	01/03/2023
4	1236	Job 1	1/3/2023 05:25:00 AM	Cristina				5	1	01/03/2023
5	1237	Job 1	1/3/2023 05:33:00 AM	Cristina				5	1	01/03/2023
6	1238	Job 1	1/3/2023 05:41:00 AM	Cristina				5	1	01/03/2023
7	1239	Job 2	1/3/2023 05:49:00 AM	Cristina				5	1	01/03/2023
8	1240	Job 2	1/3/2023 05:57:00 AM	Cristina				5	1	01/03/2023
9	1241	Job 2	1/3/2023 06:05:00 AM	Cristina				6	1	01/03/2023
10	1242	Job 2	1/3/2023 06:13:00 AM	Cristina				6	1	01/03/2023
11	1243	Job 3	1/3/2023 06:21:00 AM	Cristina				6	1	01/03/2023
12	1244	Job 3	1/3/2023 06:29:00 AM	Cristina				6	1	01/03/2023
13	1245	Job 3	1/3/2023 06:37:00 AM	Cristina				6	1	01/03/2023
14	1246	Job 3	1/3/2023 06:45:00 AM	Cristina				6	1	01/03/2023
15	1247	Job 3	1/3/2023 06:53:00 AM	Cristina				6	1	01/03/2023
16	1248	Job 3	1/3/2023 07:01:00 AM	Cristina				7	1	01/03/2023
17	1249	Job 1	1/3/2023 07:09:00 AM	Cristina				7	1	01/03/2023
18	1250	Job 1	1/3/2023 07:17:00 AM	Cristina				7	1	01/03/2023
19	1251	Job 4	1/3/2023 07:25:00 AM	Cristina				7	1	01/03/2023
20	1252	Job 4	1/3/2023 07:33:00 AM	Cristina				7	1	01/03/2023
21	1253	Job 4	1/3/2023 07:41:00 AM	Cristina				7	1	01/03/2023
22	1254	Job 4	1/3/2023 07:49:00 AM	Cristina				7	1	01/03/2023
23	1255	Job 5	1/3/2023 07:57:00 AM	Cristina				7	1	01/03/2023
24	1256	Job 5	1/3/2023 08:05:00 AM	Cristina				8	1	01/03/2023
25	1257	Job 5	1/3/2023 08:13:00 AM	Cristina				8	1	01/03/2023
26	1258	Job 5	1/3/2023 08:21:00 AM	Cristina				8	1	01/03/2023
27	1259	Job 5	1/3/2023 08:29:00 AM	Cristina				8	1	01/03/2023
28	1260	Job 5	1/3/2023 08:37:00 AM	Cristina				8	1	01/03/2023
29	1261	Job 5	1/3/2023 08:45:00 AM	Cristina				8	1	01/03/2023
30	1262	Job 5	1/3/2023 08:53:00 AM	Cristina				8	1	01/03/2023
31	1263	Job 5	1/3/2023 09:01:00 AM	Cristina				9	1	01/03/2023
32	1264	Job 5	1/3/2023 09:09:00 AM	Cristina				9	1	01/03/2023
33	1265	Job 5	1/3/2023 09:17:00 AM	Cristina				9	1	01/03/2023
34	1266	Job 5	1/3/2023 09:25:00 AM	Cristina				9	1	01/03/2023
35	1267	Job 5	1/3/2023 09:33:00 AM	Cristina				9	1	01/03/2023
36	1268	Job 5	1/3/2023 09:41:00 AM	Cristina				9	1	01/03/2023
37	1269	Job 5	1/3/2023 09:49:00 AM	Cristina				9	1	01/03/2023
38	1270	Job 5	1/3/2023 09:57:00 AM	Cristina				9	1	01/03/2023
39	1271	Job 5	1/3/2023 10:05:00 AM	Cristina				10	1	01/03/2023
40	1272	Job 5	1/3/2023 10:13:00 AM	Cristina				10	1	01/03/2023
41	1273	Job 5	1/3/2023 10:21:00 AM	Cristina				10	1	01/03/2023
42	1274	Job 6	1/3/2023 10:29:00 AM	Cristina				10	1	01/03/2023
43	1275	Job 6	1/3/2023 10:37:00 AM	Cristina				10	1	01/03/2023
44	1276	Job 6	1/3/2023 10:45:00 AM	Cristina				10	1	01/03/2023
45	1277	Job 6	1/3/2023 10:53:00 AM	Cristina				10	1	01/03/2023
46	1278	Job 6	1/3/2023 11:01:00 AM	Cristina				11	1	01/03/2023
47	1279	Job 6	1/3/2023 11:09:00 AM	Cristina				11	1	01/03/2023
48	1280	Job 6	1/3/2023 11:17:00 AM	Cristina				11	1	01/03/2023
49	1281	Job 6	1/3/2023 11:25:00 AM	Cristina				11	1	01/03/2023
50	1282	Job 6	1/3/2023 11:33:00 AM	Cristina				11	1	01/03/2023
51	1283	Job 6	1/3/2023 11:41:00 AM	Cristina				11	1	01/03/2023
52	1284	Job 6	1/3/2023 11:49:00 AM	Cristina				11	1	01/03/2023
53	1285	Job 6	1/3/2023 11:57:00 AM	Cristina				11	1	01/03/2023
54	1286	Job 6	1/3/2023 12:05:00 PM	Cristina				12	1	01/03/2023
55	1287	Job 6	1/3/2023 12:13:00 PM	Cristina				12	1	01/03/2023
56	1288	Job 6	1/3/2023 12:21:00 PM	Cristina				12	1	01/03/2023
57	1289	Job 6	1/3/2023 12:29:00 PM	Cristina				12	1	01/03/2023
58	1290	Job 6	1/3/2023 12:37:00 PM	Cristina				12	1	01/03/2023
59	1291	Job 6	1/3/2023 12:45:00 PM	Cristina				12	1	01/03/2023
60	1292	Job 6	1/3/2023 12:53:00 PM	Cristina				12	1	01/03/2023
61	1293	Job 6	1/3/2023 01:01:00 PM	Cristina				13	1	01/03/2023
62	1294	Job 6	1/3/2023 01:09:00 PM	Cristina				13	1	01/03/2023
63	1295	Job 6	1/3/2023 01:17:00 PM	Cristina				13	1	01/03/2023
64	1296	Job 6	1/3/2023 01:25:00 PM	Cristina				13	1	01/03/2023
65	1297	Job 6	1/3/2023 01:33:00 PM	Cristina				13	1	01/03/2023
66	1298	Job 6	1/3/2023 01:41:00 PM	Cristina				13	1	01/03/2023
67	1299	Job 6	1/3/2023 01:49:00 PM	Cristina				13	1	01/03/2023
68			01/03/23	Cristina	Dept ID 01	0:55	5:00	5	1	01/03/2023
69			01/03/23	Cristina	Dept ID 01	1:00	6:00	6	1	01/03/2023
70			01/03/23	Cristina	Dept ID 01	1:00	7:00	7	1	01/03/2023
71			01/03/23	Cristina	Dept ID 01	0:45	8:00	8	1	01/03/2023
72			01/03/23	Cristina	Dept ID 01	1:00	9:00	9	1	01/03/2023
73			01/03/23	Cristina	Dept ID 01	0:30	10:00	10	1	01/03/2023
74			01/03/23	Cristina	Dept ID 01	1:00	11:00	11	1	01/03/2023
75			01/03/23	Cristina	Dept ID 01	0:45	12:00	12	1	01/03/2023
76			01/03/23	Cristina	Dept ID 01	0:30	13:00	13	1	01/03/2023
Sheet1

Cell Formulas
Range	Formula
H2:H67	H2	=HOUR(C2)
I2:I76	I2	=MONTH(C2)
J2:J76	J2	=TEXT(C2,"MM/DD/YYYY")
H68:H76	H68	=HOUR(G68)

This is how i'd like to have the pivot table.

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	Hour	5	6	7	8	9	10	11	12	13	Grand Total
2	Time	0:55	1:00	1:00	0:45	1:00	0:30	1:00	0:45	0:30	7:25
3	01/03/2023	7	7	8	7	8	7	8	7	7	66
4	Job 1	5		2							7
5	Job 2	2	2								4
6	Job 3		5	1							6
7	Job 4			4							4
8	Job 5			1	7	8	3				19
9	Job 6						4	8	7	7	26
10	(blank)
11	Time	1:00	1:00	0:51	0:45	1:00	0:30	0:43	0:45	1:00	7:34
12	01/04/2023	17	20	20	20	13	10	10	10	18	138
13	Job 2								3		3
14	Job 5		13	20	8		3				44
15	Job 8				12	13	6				31
16	Job 9						1	10	7	18	36
17	Job3	17	7								24
18	(blank)
19	Grand Total	24	27	28	27	21	17	18	17	25	204
Sheet4

 

[Skybluekid]

Just a quick Question. In the Time row there are different figures 30, 45,1.00, where does this information come from?

 

[fastballfreddy]

Great question and I should have clarified. The Time row is the time each hour they are available to work. For example, if they have a 30-minute meeting it will show 0:30 instead of 1:00.

For 1/3 at the 5am hour Cristina had 0:55 of available work time and she worked 5 of Job 1 and 2 of Job 2 during that time.

Hope that helps and thanks for reviewing.

 

[fastballfreddy]

I think I figured out a solution. I updated the data and used conditional formatting to get the result I want. Probably not the best solution but it'll work.

PIVOT TABLE WORKS.xlsx
	A	B	C	D	E	F	G	H	I	J	K
4	Hour	5	6	7	8	9	10	11	12	13	Total
5	Cristina	24	27	28	27	21	17	18	17	25	205
6	01/03/2023	7	7	8	7	8	7	8	7	7	66
7	_Production	0:55	1:00	1:00	0:45	1:00	0:30	1:00	0:45	0:30	7:25
8	Job 1	5		2							7
9	Job 2	2	2								4
10	Job 3		5	1							6
11	Job 4			4							4
12	Job 5			1	7	8	3				19
13	Job 6						4	8	7	7	26
14	01/04/2023	17	20	20	20	13	10	10	10	18	138
15	_Production	1:00	1:00	0:51	0:45	1:00	0:30	0:43	0:45	1:00	7:34
16	Job 2								3		3
17	Job 5		13	20	8		3				44
18	Job 8				12	13	6				31
19	Job 9						1	10	7	18	36
20	Job3	17	7								24
PT

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5:K20	Expression	=$A5="_Production"	text	NO
B3:B4	Expression	=A3="RTAM PROD"	text	NO

PIVOT TABLE WORKS.xlsx
	A	B	C	D	E	F	G	H	I	J
1	ID	Job	date stamp	name	Dept	test	hour 2	hour	month	date
2	1234	Job 1	1/3/2023 05:09:00 AM	Cristina		1		5	1	01/03/2023
3	1235	Job 1	1/3/2023 05:17:00 AM	Cristina		1		5	1	01/03/2023
4	1236	Job 1	1/3/2023 05:25:00 AM	Cristina		1		5	1	01/03/2023
5	1237	Job 1	1/3/2023 05:33:00 AM	Cristina		1		5	1	01/03/2023
6	1238	Job 1	1/3/2023 05:41:00 AM	Cristina		1		5	1	01/03/2023
7	1239	Job 2	1/3/2023 05:49:00 AM	Cristina		1		5	1	01/03/2023
8	1240	Job 2	1/3/2023 05:57:00 AM	Cristina		1		5	1	01/03/2023
9	1241	Job 2	1/3/2023 06:05:00 AM	Cristina		1		6	1	01/03/2023
10	1242	Job 2	1/3/2023 06:13:00 AM	Cristina		1		6	1	01/03/2023
11	1243	Job 3	1/3/2023 06:21:00 AM	Cristina		1		6	1	01/03/2023
12	1244	Job 3	1/3/2023 06:29:00 AM	Cristina		1		6	1	01/03/2023
13	1245	Job 3	1/3/2023 06:37:00 AM	Cristina		1		6	1	01/03/2023
14	1246	Job 3	1/3/2023 06:45:00 AM	Cristina		1		6	1	01/03/2023
15	1247	Job 3	1/3/2023 06:53:00 AM	Cristina		1		6	1	01/03/2023
16	1248	Job 3	1/3/2023 07:01:00 AM	Cristina		1		7	1	01/03/2023
17	1249	Job 1	1/3/2023 07:09:00 AM	Cristina		1		7	1	01/03/2023
18	1250	Job 1	1/3/2023 07:17:00 AM	Cristina		1		7	1	01/03/2023
19	1251	Job 4	1/3/2023 07:25:00 AM	Cristina		1		7	1	01/03/2023
20	1252	Job 4	1/3/2023 07:33:00 AM	Cristina		1		7	1	01/03/2023
21	1253	Job 4	1/3/2023 07:41:00 AM	Cristina		1		7	1	01/03/2023
22	1254	Job 4	1/3/2023 07:49:00 AM	Cristina		1		7	1	01/03/2023
23	1255	Job 5	1/3/2023 07:57:00 AM	Cristina		1		7	1	01/03/2023
24	1256	Job 5	1/3/2023 08:05:00 AM	Cristina		1		8	1	01/03/2023
25	1257	Job 5	1/3/2023 08:13:00 AM	Cristina		1		8	1	01/03/2023
26	1258	Job 5	1/3/2023 08:21:00 AM	Cristina		1		8	1	01/03/2023
27	1259	Job 5	1/3/2023 08:29:00 AM	Cristina		1		8	1	01/03/2023
28	1260	Job 5	1/3/2023 08:37:00 AM	Cristina		1		8	1	01/03/2023
29	1261	Job 5	1/3/2023 08:45:00 AM	Cristina		1		8	1	01/03/2023
30	1262	Job 5	1/3/2023 08:53:00 AM	Cristina		1		8	1	01/03/2023
31	1263	Job 5	1/3/2023 09:01:00 AM	Cristina		1		9	1	01/03/2023
32	1264	Job 5	1/3/2023 09:09:00 AM	Cristina		1		9	1	01/03/2023
33	1265	Job 5	1/3/2023 09:17:00 AM	Cristina		1		9	1	01/03/2023
34	1266	Job 5	1/3/2023 09:25:00 AM	Cristina		1		9	1	01/03/2023
35	1267	Job 5	1/3/2023 09:33:00 AM	Cristina		1		9	1	01/03/2023
36	1268	Job 5	1/3/2023 09:41:00 AM	Cristina		1		9	1	01/03/2023
37	1269	Job 5	1/3/2023 09:49:00 AM	Cristina		1		9	1	01/03/2023
38	1270	Job 5	1/3/2023 09:57:00 AM	Cristina		1		9	1	01/03/2023
39	1271	Job 5	1/3/2023 10:05:00 AM	Cristina		1		10	1	01/03/2023
40	1272	Job 5	1/3/2023 10:13:00 AM	Cristina		1		10	1	01/03/2023
41	1273	Job 5	1/3/2023 10:21:00 AM	Cristina		1		10	1	01/03/2023
42	1274	Job 6	1/3/2023 10:29:00 AM	Cristina		1		10	1	01/03/2023
43	1275	Job 6	1/3/2023 10:37:00 AM	Cristina		1		10	1	01/03/2023
44	1276	Job 6	1/3/2023 10:45:00 AM	Cristina		1		10	1	01/03/2023
45	1277	Job 6	1/3/2023 10:53:00 AM	Cristina		1		10	1	01/03/2023
46	1278	Job 6	1/3/2023 11:01:00 AM	Cristina		1		11	1	01/03/2023
47	1279	Job 6	1/3/2023 11:09:00 AM	Cristina		1		11	1	01/03/2023
48	1280	Job 6	1/3/2023 11:17:00 AM	Cristina		1		11	1	01/03/2023
49	1281	Job 6	1/3/2023 11:25:00 AM	Cristina		1		11	1	01/03/2023
50	1282	Job 6	1/3/2023 11:33:00 AM	Cristina		1		11	1	01/03/2023
51	1283	Job 6	1/3/2023 11:41:00 AM	Cristina		1		11	1	01/03/2023
52	1284	Job 6	1/3/2023 11:49:00 AM	Cristina		1		11	1	01/03/2023
53	1285	Job 6	1/3/2023 11:57:00 AM	Cristina		1		11	1	01/03/2023
54	1286	Job 6	1/3/2023 12:05:00 PM	Cristina		1		12	1	01/03/2023
55	1287	Job 6	1/3/2023 12:13:00 PM	Cristina		1		12	1	01/03/2023
56	1288	Job 6	1/3/2023 12:21:00 PM	Cristina		1		12	1	01/03/2023
57	1289	Job 6	1/3/2023 12:29:00 PM	Cristina		1		12	1	01/03/2023
58	1290	Job 6	1/3/2023 12:37:00 PM	Cristina		1		12	1	01/03/2023
59	1291	Job 6	1/3/2023 12:45:00 PM	Cristina		1		12	1	01/03/2023
60	1292	Job 6	1/3/2023 12:53:00 PM	Cristina		1		12	1	01/03/2023
61	1293	Job 6	1/3/2023 01:01:00 PM	Cristina		1		13	1	01/03/2023
62	1294	Job 6	1/3/2023 01:09:00 PM	Cristina		1		13	1	01/03/2023
63	1295	Job 6	1/3/2023 01:17:00 PM	Cristina		1		13	1	01/03/2023
64	1296	Job 6	1/3/2023 01:25:00 PM	Cristina		1		13	1	01/03/2023
65	1297	Job 6	1/3/2023 01:33:00 PM	Cristina		1		13	1	01/03/2023
66	1298	Job 6	1/3/2023 01:41:00 PM	Cristina		1		13	1	01/03/2023
67	1299	Job 6	1/3/2023 01:49:00 PM	Cristina		1		13	1	01/03/2023
68		_Production	01/03/23	Cristina	Dept ID 01	0:55	5:00	5	1	01/03/2023
69		_Production	01/03/23	Cristina	Dept ID 01	1:00	6:00	6	1	01/03/2023
70		_Production	01/03/23	Cristina	Dept ID 01	1:00	7:00	7	1	01/03/2023
71		_Production	01/03/23	Cristina	Dept ID 01	0:45	8:00	8	1	01/03/2023
72		_Production	01/03/23	Cristina	Dept ID 01	1:00	9:00	9	1	01/03/2023
73		_Production	01/03/23	Cristina	Dept ID 01	0:30	10:00	10	1	01/03/2023
74		_Production	01/03/23	Cristina	Dept ID 01	1:00	11:00	11	1	01/03/2023
75		_Production	01/03/23	Cristina	Dept ID 01	0:45	12:00	12	1	01/03/2023
76		_Production	01/03/23	Cristina	Dept ID 01	0:30	13:00	13	1	01/03/2023
DATA

Cell Formulas
Range	Formula
H2:H67	H2	=HOUR(C2)
I2:I76	I2	=MONTH(C2)
J2:J76	J2	=TEXT(C2,"MM/DD/YYYY")
H68:H76	H68	=HOUR(G68)