change data depending on the date

[chaboyski]

Hi Team,

I'm thinking of using IFS for sorting out the Code on the table below where in it would show PLTO: PLANNED TIME OFF instead of CTR: SHIFT but it would depend on the date. other entries doesn't play any part. the main thing is to convert SHIFT to PLTO if they are on the same date. also, once done, how can i show it into a summary tab where in if it would be shift, it would show the start time and if its PLTO, it would show PLTO based on the given below.

we can add like something to define it after column M.

Dump File Tab:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	First Name	Last Name	ID	Code	Description	Rank	Nominal Date	Day of Week	Start	Start Time of Day	Stop	Stop Time of Day	Duration
2	Employee 1	Last Name	9999	CTR: SHIFT	SHIFT	2038	5/22/2023	Monday	08:00 AM	08:00 AM	05:00 PM	05:00 PM	09:00
3	Employee 2	Last Name	8888	BRK: BREAK 2	BREAK 2	36	5/22/2023	Monday	01:00 PM	01:00 PM	01:15 PM	01:15 PM	00:15
4	Employee 2	Last Name	8888	PLTO: PLANNED TIME OFF	PLANNED TIME OFF	14	5/22/2023	Monday	08:00 AM	08:00 AM	05:00 PM	05:00 PM	09:00
5	Employee 2	Last Name	8888	MEAL: UNPAID MEAL REGULAR	UNPAID MEAL REGULAR	2	5/22/2023	Monday	03:00 PM	03:00 PM	04:00 PM	04:00 PM	01:00
6	Employee 1	Last Name	9999	MEAL: UNPAID MEAL REGULAR	UNPAID MEAL REGULAR	2	5/22/2023	Monday	11:45 AM	11:45 AM	12:45 PM	12:45 PM	01:00
7	Employee 1	Last Name	9999	WRK: PROGRAM H PROV SVC WI	PROGRAM H PROV SVC WI	1904	5/22/2023	Monday	08:00 AM	08:00 AM	05:00 PM	05:00 PM	09:00
8	Employee 2	Last Name	8888	WRK: PROGRAM H PROV SVC WI	PROGRAM H PROV SVC WI	1904	5/22/2023	Monday	08:00 AM	08:00 AM	05:00 PM	05:00 PM	09:00
9	Employee 2	Last Name	8888	BRK: BREAK 1	BREAK 1	35	5/22/2023	Monday	10:30 AM	10:30 AM	10:45 AM	10:45 AM	00:15
10	Employee 2	Last Name	8888	CTR: SHIFT	SHIFT	2038	5/22/2023	Monday	08:00 AM	08:00 AM	05:00 PM	05:00 PM	09:00
11	Employee 1	Last Name	9999	BRK: BREAK 1	BREAK 1	35	5/22/2023	Monday	10:00 AM	10:00 AM	10:15 AM	10:15 AM	00:15
12	Employee 1	Last Name	9999	BRK: BREAK 2	BREAK 2	36	5/22/2023	Monday	02:15 PM	02:15 PM	02:30 PM	02:30 PM	00:15
13	Employee 1	Last Name	9999	OP: COACHING	COACHING	57	5/23/2023	Tuesday	01:00 PM	01:00 PM	01:30 PM	01:30 PM	00:30
14	Employee 2	Last Name	8888	CTR: SHIFT	SHIFT	2038	5/23/2023	Tuesday	08:00 AM	08:00 AM	05:00 PM	05:00 PM	09:00
15	Employee 1	Last Name	9999	BRK: BREAK 2	BREAK 2	36	5/23/2023	Tuesday	02:15 PM	02:15 PM	02:30 PM	02:30 PM	00:15
16	Employee 2	Last Name	8888	BRK: BREAK 2	BREAK 2	36	5/23/2023	Tuesday	01:00 PM	01:00 PM	01:15 PM	01:15 PM	00:15
17	Employee 1	Last Name	9999	WRK: PROGRAM H PROV SVC WI	PROGRAM H PROV SVC WI	1904	5/23/2023	Tuesday	08:00 AM	08:00 AM	05:00 PM	05:00 PM	09:00
18	Employee 1	Last Name	9999	BRK: BREAK 1	BREAK 1	35	5/23/2023	Tuesday	10:00 AM	10:00 AM	10:15 AM	10:15 AM	00:15
19	Employee 2	Last Name	8888	WRK: PROGRAM H PROV SVC WI	PROGRAM H PROV SVC WI	1904	5/23/2023	Tuesday	08:00 AM	08:00 AM	05:00 PM	05:00 PM	09:00
20	Employee 1	Last Name	9999	CTR: SHIFT	SHIFT	2038	5/23/2023	Tuesday	08:00 AM	08:00 AM	05:00 PM	05:00 PM	09:00
21	Employee 2	Last Name	8888	MEAL: UNPAID MEAL REGULAR	UNPAID MEAL REGULAR	2	5/23/2023	Tuesday	03:00 PM	03:00 PM	04:00 PM	04:00 PM	01:00
22	Employee 1	Last Name	9999	MEAL: UNPAID MEAL REGULAR	UNPAID MEAL REGULAR	2	5/23/2023	Tuesday	11:45 AM	11:45 AM	12:45 PM	12:45 PM	01:00
23	Employee 2	Last Name	8888	PLTO: PLANNED TIME OFF	PLANNED TIME OFF	14	5/23/2023	Tuesday	08:00 AM	08:00 AM	05:00 PM	05:00 PM	09:00
24	Employee 2	Last Name	8888	BRK: BREAK 1	BREAK 1	35	5/23/2023	Tuesday	10:30 AM	10:30 AM	10:45 AM	10:45 AM	00:15
Dump

Summary Tab:

Book1
	C	D	E
4	Employee ID	5/22/2023	5/23/2023
5	9999	PLTO: PLANNED TIME OFF	PLTO: PLANNED TIME OFF
6	8888	8:00	8:00
Summary

Thank you in advance

 

[etaf]

does this work for you
=IF(AND(D4="CTR: SHIFT",COUNTIFS($D$2:$D$24,"PLTO: PLANNED TIME OFF",$A$2:$A$24,A4,$G$2:$G$24,G4)>0),"PLTO: PLANNED TIME OFF",D4)

Book11
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	First Name	Last Name	ID	Code	Description	Rank	Nominal Date	Day of Week	Start	Start Time of Day	Stop	Stop Time of Day	Duration
2	Employee 1	Last Name	9999	CTR: SHIFT	SHIFT	2038	5/22/23	Monday	45068.33333	0.333333333	45068.70833	0.708333333	0.375		CTR: SHIFT
3	Employee 2	Last Name	8888	BRK: BREAK 2	BREAK 2	36	5/22/23	Monday	45068.54167	0.541666667	45068.55208	0.552083333	0.010416667		BRK: BREAK 2
4	Employee 2	Last Name	8888	PLTO: PLANNED TIME OFF	PLANNED TIME OFF	14	5/22/23	Monday	45068.33333	0.333333333	45068.70833	0.708333333	0.375		PLTO: PLANNED TIME OFF
5	Employee 2	Last Name	8888	MEAL: UNPAID MEAL REGULAR	UNPAID MEAL REGULAR	2	5/22/23	Monday	45068.625	0.625	45068.66667	0.666666667	0.041666667		MEAL: UNPAID MEAL REGULAR
6	Employee 1	Last Name	9999	MEAL: UNPAID MEAL REGULAR	UNPAID MEAL REGULAR	2	5/22/23	Monday	45068.48958	0.489583333	45068.53125	0.53125	0.041666667		MEAL: UNPAID MEAL REGULAR
7	Employee 1	Last Name	9999	WRK: PROGRAM H PROV SVC WI	PROGRAM H PROV SVC WI	1904	5/22/23	Monday	45068.33333	0.333333333	45068.70833	0.708333333	0.375		WRK: PROGRAM H PROV SVC WI
8	Employee 2	Last Name	8888	WRK: PROGRAM H PROV SVC WI	PROGRAM H PROV SVC WI	1904	5/22/23	Monday	45068.33333	0.333333333	45068.70833	0.708333333	0.375		WRK: PROGRAM H PROV SVC WI
9	Employee 2	Last Name	8888	BRK: BREAK 1	BREAK 1	35	5/22/23	Monday	45068.4375	0.4375	45068.44792	0.447916667	0.010416667		BRK: BREAK 1
10	Employee 2	Last Name	8888	CTR: SHIFT	SHIFT	2038	5/22/23	Monday	45068.33333	0.333333333	45068.70833	0.708333333	0.375		PLTO: PLANNED TIME OFF
11	Employee 1	Last Name	9999	BRK: BREAK 1	BREAK 1	35	5/22/23	Monday	45068.41667	0.416666667	45068.42708	0.427083333	0.010416667		BRK: BREAK 1
12	Employee 1	Last Name	9999	BRK: BREAK 2	BREAK 2	36	5/22/23	Monday	45068.59375	0.59375	45068.60417	0.604166667	0.010416667		BRK: BREAK 2
13	Employee 1	Last Name	9999	OP: COACHING	COACHING	57	5/23/23	Tuesday	45069.54167	0.541666667	45069.5625	0.5625	0.020833333		OP: COACHING
14	Employee 2	Last Name	8888	CTR: SHIFT	SHIFT	2038	5/23/23	Tuesday	45069.33333	0.333333333	45069.70833	0.708333333	0.375		PLTO: PLANNED TIME OFF
15	Employee 1	Last Name	9999	BRK: BREAK 2	BREAK 2	36	5/23/23	Tuesday	45069.59375	0.59375	45069.60417	0.604166667	0.010416667		BRK: BREAK 2
16	Employee 2	Last Name	8888	BRK: BREAK 2	BREAK 2	36	5/23/23	Tuesday	45069.54167	0.541666667	45069.55208	0.552083333	0.010416667		BRK: BREAK 2
17	Employee 1	Last Name	9999	WRK: PROGRAM H PROV SVC WI	PROGRAM H PROV SVC WI	1904	5/23/23	Tuesday	45069.33333	0.333333333	45069.70833	0.708333333	0.375		WRK: PROGRAM H PROV SVC WI
18	Employee 1	Last Name	9999	BRK: BREAK 1	BREAK 1	35	5/23/23	Tuesday	45069.41667	0.416666667	45069.42708	0.427083333	0.010416667		BRK: BREAK 1
19	Employee 2	Last Name	8888	WRK: PROGRAM H PROV SVC WI	PROGRAM H PROV SVC WI	1904	5/23/23	Tuesday	45069.33333	0.333333333	45069.70833	0.708333333	0.375		WRK: PROGRAM H PROV SVC WI
20	Employee 1	Last Name	9999	CTR: SHIFT	SHIFT	2038	5/23/23	Tuesday	45069.33333	0.333333333	45069.70833	0.708333333	0.375		CTR: SHIFT
21	Employee 2	Last Name	8888	MEAL: UNPAID MEAL REGULAR	UNPAID MEAL REGULAR	2	5/23/23	Tuesday	45069.625	0.625	45069.66667	0.666666667	0.041666667		MEAL: UNPAID MEAL REGULAR
22	Employee 1	Last Name	9999	MEAL: UNPAID MEAL REGULAR	UNPAID MEAL REGULAR	2	5/23/23	Tuesday	45069.48958	0.489583333	45069.53125	0.53125	0.041666667		MEAL: UNPAID MEAL REGULAR
23	Employee 2	Last Name	8888	PLTO: PLANNED TIME OFF	PLANNED TIME OFF	14	5/23/23	Tuesday	45069.33333	0.333333333	45069.70833	0.708333333	0.375		PLTO: PLANNED TIME OFF
24	Employee 2	Last Name	8888	BRK: BREAK 1	BREAK 1	35	5/23/23	Tuesday	45069.4375	0.4375	45069.44792	0.447916667	0.010416667		BRK: BREAK 1
Sheet1

Cell Formulas
Range	Formula
O2:O24	O2	=IF(AND(D2="CTR: SHIFT",COUNTIFS($D$2:$D$24,"PLTO: PLANNED TIME OFF",$A$2:$A$24,A2,$G$2:$G$24,G2)>0),"PLTO: PLANNED TIME OFF",D2)

 

[etaf]

i noticed in the formula i posted , it was referencing D4 , XL2BB is OK , correct reference


should have been in the post text
=IF(AND(D2="CTR: SHIFT",COUNTIFS($D$2:$D$24,"PLTO: PLANNED TIME OFF",$A$2:$A$24,A2,$G$2:$G$24,G2)>0),"PLTO: PLANNED TIME OFF",D2)

 

[chaboyski]

thank you, @etaf it worked on coverting the Shift into PLTO

now, i'm wondering how i can use that to look it up, should i use IFS with vlookup? since there are multiple entries(breaks, lunch, etc), i just need to get the Time of the Shift, and if the shift is PLTO, just say PLTO.

 

[etaf]

not sure i'm understanding that fully

this will give the time of the START , column I - based on the cell with Shift , that has changed to PLTO: PLANNED TIME OFF

you could do this to get the time , when the original cell = "CTR: SHIFT" and the updated cell = "PLTO: PLANNED TIME OFF"
column P = time of shift - 1
=IF(AND(D2="CTR: SHIFT",O2="PLTO: PLANNED TIME OFF"),TIME(HOUR(I2),MINUTE(I2),SECOND(I2)),"")

or you could modify the code that we used before
column Q = time of shift - 2
=IF(AND(D2="CTR: SHIFT",COUNTIFS($D$2:$D$24,"PLTO: PLANNED TIME OFF",$A$2:$A$24,A2,$G$2:$G$24,G2)>0),TIME(HOUR(I2),MINUTE(I2),SECOND(I2)),"")

OR , if you want the time when it says SHIFT - BUT does not have the PLTO changed
and when the shift has change to PLTO: PLANNED TIME OFF, just put PLTO

column R = Shift TIME if NOT PLTO
=IF(AND(D2="CTR: SHIFT",O2="PLTO: PLANNED TIME OFF"),"PLTO",IF(D2="CTR: SHIFT",TIME(HOUR(I2),MINUTE(I2),SECOND(I2)),""))

otherwise , you will need to explain further , with examples

Book11
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	First Name	Last Name	ID	Code	Description	Rank	Nominal Date	Day of Week	Start	Start Time of Day	Stop	Stop Time of Day	Duration			time of shift - 1	time of shift-2	Shift TIME if NOT PLTO
2	Employee 1	Last Name	9999	CTR: SHIFT	SHIFT	2038	5/22/23	Monday	5/22/23 8:00	1/0/00 8:00	5/22/23 17:00	1/0/00 17:00	0.375		CTR: SHIFT			08:00:00
3	Employee 2	Last Name	8888	BRK: BREAK 2	BREAK 2	36	5/22/23	Monday	5/22/23 13:00	1/0/00 13:00	5/22/23 13:15	1/0/00 13:15	0.010416667		BRK: BREAK 2
4	Employee 2	Last Name	8888	PLTO: PLANNED TIME OFF	PLANNED TIME OFF	14	5/22/23	Monday	5/22/23 8:00	1/0/00 8:00	5/22/23 17:00	1/0/00 17:00	0.375		PLTO: PLANNED TIME OFF
5	Employee 2	Last Name	8888	MEAL: UNPAID MEAL REGULAR	UNPAID MEAL REGULAR	2	5/22/23	Monday	5/22/23 15:00	1/0/00 15:00	5/22/23 16:00	1/0/00 16:00	0.041666667		MEAL: UNPAID MEAL REGULAR
6	Employee 1	Last Name	9999	MEAL: UNPAID MEAL REGULAR	UNPAID MEAL REGULAR	2	5/22/23	Monday	5/22/23 11:45	1/0/00 11:45	5/22/23 12:45	1/0/00 12:45	0.041666667		MEAL: UNPAID MEAL REGULAR
7	Employee 1	Last Name	9999	WRK: PROGRAM H PROV SVC WI	PROGRAM H PROV SVC WI	1904	5/22/23	Monday	5/22/23 8:00	1/0/00 8:00	5/22/23 17:00	1/0/00 17:00	0.375		WRK: PROGRAM H PROV SVC WI
8	Employee 2	Last Name	8888	WRK: PROGRAM H PROV SVC WI	PROGRAM H PROV SVC WI	1904	5/22/23	Monday	5/22/23 8:00	1/0/00 8:00	5/22/23 17:00	1/0/00 17:00	0.375		WRK: PROGRAM H PROV SVC WI
9	Employee 2	Last Name	8888	BRK: BREAK 1	BREAK 1	35	5/22/23	Monday	5/22/23 10:30	1/0/00 10:30	5/22/23 10:45	1/0/00 10:45	0.010416667		BRK: BREAK 1
10	Employee 2	Last Name	8888	CTR: SHIFT	SHIFT	2038	5/22/23	Monday	5/22/23 8:00	1/0/00 8:00	5/22/23 17:00	1/0/00 17:00	0.375		PLTO: PLANNED TIME OFF	08:00:00	08:00:00	PLTO
11	Employee 1	Last Name	9999	BRK: BREAK 1	BREAK 1	35	5/22/23	Monday	5/22/23 10:00	1/0/00 10:00	5/22/23 10:15	1/0/00 10:15	0.010416667		BRK: BREAK 1
12	Employee 1	Last Name	9999	BRK: BREAK 2	BREAK 2	36	5/22/23	Monday	5/22/23 14:15	1/0/00 14:15	5/22/23 14:30	1/0/00 14:30	0.010416667		BRK: BREAK 2
13	Employee 1	Last Name	9999	OP: COACHING	COACHING	57	5/23/23	Tuesday	5/23/23 13:00	1/0/00 13:00	5/23/23 13:30	1/0/00 13:30	0.020833333		OP: COACHING
14	Employee 2	Last Name	8888	CTR: SHIFT	SHIFT	2038	5/23/23	Tuesday	5/23/23 8:00	1/0/00 8:00	5/23/23 17:00	1/0/00 17:00	0.375		PLTO: PLANNED TIME OFF	08:00:00	08:00:00	PLTO
15	Employee 1	Last Name	9999	BRK: BREAK 2	BREAK 2	36	5/23/23	Tuesday	5/23/23 14:15	1/0/00 14:15	5/23/23 14:30	1/0/00 14:30	0.010416667		BRK: BREAK 2
16	Employee 2	Last Name	8888	BRK: BREAK 2	BREAK 2	36	5/23/23	Tuesday	5/23/23 13:00	1/0/00 13:00	5/23/23 13:15	1/0/00 13:15	0.010416667		BRK: BREAK 2
17	Employee 1	Last Name	9999	WRK: PROGRAM H PROV SVC WI	PROGRAM H PROV SVC WI	1904	5/23/23	Tuesday	5/23/23 8:00	1/0/00 8:00	5/23/23 17:00	1/0/00 17:00	0.375		WRK: PROGRAM H PROV SVC WI
18	Employee 1	Last Name	9999	BRK: BREAK 1	BREAK 1	35	5/23/23	Tuesday	5/23/23 10:00	1/0/00 10:00	5/23/23 10:15	1/0/00 10:15	0.010416667		BRK: BREAK 1
19	Employee 2	Last Name	8888	WRK: PROGRAM H PROV SVC WI	PROGRAM H PROV SVC WI	1904	5/23/23	Tuesday	5/23/23 8:00	1/0/00 8:00	5/23/23 17:00	1/0/00 17:00	0.375		WRK: PROGRAM H PROV SVC WI
20	Employee 1	Last Name	9999	CTR: SHIFT	SHIFT	2038	5/23/23	Tuesday	5/23/23 8:00	1/0/00 8:00	5/23/23 17:00	1/0/00 17:00	0.375		CTR: SHIFT			08:00:00
21	Employee 2	Last Name	8888	MEAL: UNPAID MEAL REGULAR	UNPAID MEAL REGULAR	2	5/23/23	Tuesday	5/23/23 15:00	1/0/00 15:00	5/23/23 16:00	1/0/00 16:00	0.041666667		MEAL: UNPAID MEAL REGULAR
22	Employee 1	Last Name	9999	MEAL: UNPAID MEAL REGULAR	UNPAID MEAL REGULAR	2	5/23/23	Tuesday	5/23/23 11:45	1/0/00 11:45	5/23/23 12:45	1/0/00 12:45	0.041666667		MEAL: UNPAID MEAL REGULAR
23	Employee 2	Last Name	8888	PLTO: PLANNED TIME OFF	PLANNED TIME OFF	14	5/23/23	Tuesday	5/23/23 8:00	1/0/00 8:00	5/23/23 17:00	1/0/00 17:00	0.375		PLTO: PLANNED TIME OFF
24	Employee 2	Last Name	8888	BRK: BREAK 1	BREAK 1	35	5/23/23	Tuesday	5/23/23 10:30	1/0/00 10:30	5/23/23 10:45	1/0/00 10:45	0.010416667		BRK: BREAK 1
Sheet1

Cell Formulas
Range	Formula
O2:O24	O2	=IF(AND(D2="CTR: SHIFT",COUNTIFS($D$2:$D$24,"PLTO: PLANNED TIME OFF",$A$2:$A$24,A2,$G$2:$G$24,G2)>0),"PLTO: PLANNED TIME OFF",D2)
P2:P24	P2	=IF(AND(D2="CTR: SHIFT",O2="PLTO: PLANNED TIME OFF"),TIME(HOUR(I2),MINUTE(I2),SECOND(I2)),"")
Q2:Q24	Q2	=IF(AND(D2="CTR: SHIFT",COUNTIFS($D$2:$D$24,"PLTO: PLANNED TIME OFF",$A$2:$A$24,A2,$G$2:$G$24,G2)>0),TIME(HOUR(I2),MINUTE(I2),SECOND(I2)),"")
R2:R24	R2	=IF(AND(D2="CTR: SHIFT",O2="PLTO: PLANNED TIME OFF"),"PLTO",IF(D2="CTR: SHIFT",TIME(HOUR(I2),MINUTE(I2),SECOND(I2)),""))

 

[chaboyski]

I think everything is clear now. This helps a lot! I would need to just use pivot to get what i need instead since my source (date) is vertical while im trying to represent it as horizontal. Thank you so much!

 

[etaf]

you are welcome
are you still using 2016 version, s 365 has a lot more functions and may help with converting vertical to horizontal

 

[chaboyski]

yeah, sad to say my company only has 2016 lol

 

[etaf]

ok, the functions i used will work with 2016