change data depending on the date

C

chaboyski

Board Regular
Joined
Aug 18, 2020
Messages
59
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGHIJKLM
1First NameLast NameIDCodeDescriptionRankNominal DateDay of WeekStartStart Time of DayStopStop Time of DayDuration
2Employee 1Last Name9999CTR: SHIFTSHIFT20385/22/2023Monday08:00 AM08:00 AM05:00 PM05:00 PM09:00
3Employee 2Last Name8888BRK: BREAK 2BREAK 2365/22/2023Monday01:00 PM01:00 PM01:15 PM01:15 PM00:15
4Employee 2Last Name8888PLTO: PLANNED TIME OFFPLANNED TIME OFF145/22/2023Monday08:00 AM08:00 AM05:00 PM05:00 PM09:00
5Employee 2Last Name8888MEAL: UNPAID MEAL REGULARUNPAID MEAL REGULAR25/22/2023Monday03:00 PM03:00 PM04:00 PM04:00 PM01:00
6Employee 1Last Name9999MEAL: UNPAID MEAL REGULARUNPAID MEAL REGULAR25/22/2023Monday11:45 AM11:45 AM12:45 PM12:45 PM01:00
7Employee 1Last Name9999WRK: PROGRAM H PROV SVC WIPROGRAM H PROV SVC WI19045/22/2023Monday08:00 AM08:00 AM05:00 PM05:00 PM09:00
8Employee 2Last Name8888WRK: PROGRAM H PROV SVC WIPROGRAM H PROV SVC WI19045/22/2023Monday08:00 AM08:00 AM05:00 PM05:00 PM09:00
9Employee 2Last Name8888BRK: BREAK 1BREAK 1355/22/2023Monday10:30 AM10:30 AM10:45 AM10:45 AM00:15
10Employee 2Last Name8888CTR: SHIFTSHIFT20385/22/2023Monday08:00 AM08:00 AM05:00 PM05:00 PM09:00
11Employee 1Last Name9999BRK: BREAK 1BREAK 1355/22/2023Monday10:00 AM10:00 AM10:15 AM10:15 AM00:15
12Employee 1Last Name9999BRK: BREAK 2BREAK 2365/22/2023Monday02:15 PM02:15 PM02:30 PM02:30 PM00:15
13Employee 1Last Name9999OP: COACHINGCOACHING575/23/2023Tuesday01:00 PM01:00 PM01:30 PM01:30 PM00:30
14Employee 2Last Name8888CTR: SHIFTSHIFT20385/23/2023Tuesday08:00 AM08:00 AM05:00 PM05:00 PM09:00
15Employee 1Last Name9999BRK: BREAK 2BREAK 2365/23/2023Tuesday02:15 PM02:15 PM02:30 PM02:30 PM00:15
16Employee 2Last Name8888BRK: BREAK 2BREAK 2365/23/2023Tuesday01:00 PM01:00 PM01:15 PM01:15 PM00:15
17Employee 1Last Name9999WRK: PROGRAM H PROV SVC WIPROGRAM H PROV SVC WI19045/23/2023Tuesday08:00 AM08:00 AM05:00 PM05:00 PM09:00
18Employee 1Last Name9999BRK: BREAK 1BREAK 1355/23/2023Tuesday10:00 AM10:00 AM10:15 AM10:15 AM00:15
19Employee 2Last Name8888WRK: PROGRAM H PROV SVC WIPROGRAM H PROV SVC WI19045/23/2023Tuesday08:00 AM08:00 AM05:00 PM05:00 PM09:00
20Employee 1Last Name9999CTR: SHIFTSHIFT20385/23/2023Tuesday08:00 AM08:00 AM05:00 PM05:00 PM09:00
21Employee 2Last Name8888MEAL: UNPAID MEAL REGULARUNPAID MEAL REGULAR25/23/2023Tuesday03:00 PM03:00 PM04:00 PM04:00 PM01:00
22Employee 1Last Name9999MEAL: UNPAID MEAL REGULARUNPAID MEAL REGULAR25/23/2023Tuesday11:45 AM11:45 AM12:45 PM12:45 PM01:00
23Employee 2Last Name8888PLTO: PLANNED TIME OFFPLANNED TIME OFF145/23/2023Tuesday08:00 AM08:00 AM05:00 PM05:00 PM09:00
24Employee 2Last Name8888BRK: BREAK 1BREAK 1355/23/2023Tuesday10:30 AM10:30 AM10:45 AM10:45 AM00:15
Dump


Summary Tab:

Book1
CDE
4Employee ID5/22/20235/23/2023
59999PLTO: PLANNED TIME OFFPLTO: PLANNED TIME OFF
688888:008:00
Summary


Thank you in advance
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
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
ABCDEFGHIJKLMNO
1First NameLast NameIDCodeDescriptionRankNominal DateDay of WeekStartStart Time of DayStopStop Time of DayDuration
2Employee 1Last Name9999CTR: SHIFTSHIFT20385/22/23Monday45068.333330.33333333345068.708330.7083333330.375CTR: SHIFT
3Employee 2Last Name8888BRK: BREAK 2BREAK 2365/22/23Monday45068.541670.54166666745068.552080.5520833330.010416667BRK: BREAK 2
4Employee 2Last Name8888PLTO: PLANNED TIME OFFPLANNED TIME OFF145/22/23Monday45068.333330.33333333345068.708330.7083333330.375PLTO: PLANNED TIME OFF
5Employee 2Last Name8888MEAL: UNPAID MEAL REGULARUNPAID MEAL REGULAR25/22/23Monday45068.6250.62545068.666670.6666666670.041666667MEAL: UNPAID MEAL REGULAR
6Employee 1Last Name9999MEAL: UNPAID MEAL REGULARUNPAID MEAL REGULAR25/22/23Monday45068.489580.48958333345068.531250.531250.041666667MEAL: UNPAID MEAL REGULAR
7Employee 1Last Name9999WRK: PROGRAM H PROV SVC WIPROGRAM H PROV SVC WI19045/22/23Monday45068.333330.33333333345068.708330.7083333330.375WRK: PROGRAM H PROV SVC WI
8Employee 2Last Name8888WRK: PROGRAM H PROV SVC WIPROGRAM H PROV SVC WI19045/22/23Monday45068.333330.33333333345068.708330.7083333330.375WRK: PROGRAM H PROV SVC WI
9Employee 2Last Name8888BRK: BREAK 1BREAK 1355/22/23Monday45068.43750.437545068.447920.4479166670.010416667BRK: BREAK 1
10Employee 2Last Name8888CTR: SHIFTSHIFT20385/22/23Monday45068.333330.33333333345068.708330.7083333330.375PLTO: PLANNED TIME OFF
11Employee 1Last Name9999BRK: BREAK 1BREAK 1355/22/23Monday45068.416670.41666666745068.427080.4270833330.010416667BRK: BREAK 1
12Employee 1Last Name9999BRK: BREAK 2BREAK 2365/22/23Monday45068.593750.5937545068.604170.6041666670.010416667BRK: BREAK 2
13Employee 1Last Name9999OP: COACHINGCOACHING575/23/23Tuesday45069.541670.54166666745069.56250.56250.020833333OP: COACHING
14Employee 2Last Name8888CTR: SHIFTSHIFT20385/23/23Tuesday45069.333330.33333333345069.708330.7083333330.375PLTO: PLANNED TIME OFF
15Employee 1Last Name9999BRK: BREAK 2BREAK 2365/23/23Tuesday45069.593750.5937545069.604170.6041666670.010416667BRK: BREAK 2
16Employee 2Last Name8888BRK: BREAK 2BREAK 2365/23/23Tuesday45069.541670.54166666745069.552080.5520833330.010416667BRK: BREAK 2
17Employee 1Last Name9999WRK: PROGRAM H PROV SVC WIPROGRAM H PROV SVC WI19045/23/23Tuesday45069.333330.33333333345069.708330.7083333330.375WRK: PROGRAM H PROV SVC WI
18Employee 1Last Name9999BRK: BREAK 1BREAK 1355/23/23Tuesday45069.416670.41666666745069.427080.4270833330.010416667BRK: BREAK 1
19Employee 2Last Name8888WRK: PROGRAM H PROV SVC WIPROGRAM H PROV SVC WI19045/23/23Tuesday45069.333330.33333333345069.708330.7083333330.375WRK: PROGRAM H PROV SVC WI
20Employee 1Last Name9999CTR: SHIFTSHIFT20385/23/23Tuesday45069.333330.33333333345069.708330.7083333330.375CTR: SHIFT
21Employee 2Last Name8888MEAL: UNPAID MEAL REGULARUNPAID MEAL REGULAR25/23/23Tuesday45069.6250.62545069.666670.6666666670.041666667MEAL: UNPAID MEAL REGULAR
22Employee 1Last Name9999MEAL: UNPAID MEAL REGULARUNPAID MEAL REGULAR25/23/23Tuesday45069.489580.48958333345069.531250.531250.041666667MEAL: UNPAID MEAL REGULAR
23Employee 2Last Name8888PLTO: PLANNED TIME OFFPLANNED TIME OFF145/23/23Tuesday45069.333330.33333333345069.708330.7083333330.375PLTO: PLANNED TIME OFF
24Employee 2Last Name8888BRK: BREAK 1BREAK 1355/23/23Tuesday45069.43750.437545069.447920.4479166670.010416667BRK: BREAK 1
Sheet1
Cell Formulas
RangeFormula
O2:O24O2=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)
 
Upvote 0
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)
 
Upvote 1
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.
 
Upvote 0
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
ABCDEFGHIJKLMNOPQR
1First NameLast NameIDCodeDescriptionRankNominal DateDay of WeekStartStart Time of DayStopStop Time of DayDurationtime of shift - 1time of shift-2Shift TIME if NOT PLTO
2Employee 1Last Name9999CTR: SHIFTSHIFT20385/22/23Monday5/22/23 8:001/0/00 8:005/22/23 17:001/0/00 17:000.375CTR: SHIFT  08:00:00
3Employee 2Last Name8888BRK: BREAK 2BREAK 2365/22/23Monday5/22/23 13:001/0/00 13:005/22/23 13:151/0/00 13:150.010416667BRK: BREAK 2   
4Employee 2Last Name8888PLTO: PLANNED TIME OFFPLANNED TIME OFF145/22/23Monday5/22/23 8:001/0/00 8:005/22/23 17:001/0/00 17:000.375PLTO: PLANNED TIME OFF   
5Employee 2Last Name8888MEAL: UNPAID MEAL REGULARUNPAID MEAL REGULAR25/22/23Monday5/22/23 15:001/0/00 15:005/22/23 16:001/0/00 16:000.041666667MEAL: UNPAID MEAL REGULAR   
6Employee 1Last Name9999MEAL: UNPAID MEAL REGULARUNPAID MEAL REGULAR25/22/23Monday5/22/23 11:451/0/00 11:455/22/23 12:451/0/00 12:450.041666667MEAL: UNPAID MEAL REGULAR   
7Employee 1Last Name9999WRK: PROGRAM H PROV SVC WIPROGRAM H PROV SVC WI19045/22/23Monday5/22/23 8:001/0/00 8:005/22/23 17:001/0/00 17:000.375WRK: PROGRAM H PROV SVC WI   
8Employee 2Last Name8888WRK: PROGRAM H PROV SVC WIPROGRAM H PROV SVC WI19045/22/23Monday5/22/23 8:001/0/00 8:005/22/23 17:001/0/00 17:000.375WRK: PROGRAM H PROV SVC WI   
9Employee 2Last Name8888BRK: BREAK 1BREAK 1355/22/23Monday5/22/23 10:301/0/00 10:305/22/23 10:451/0/00 10:450.010416667BRK: BREAK 1   
10Employee 2Last Name8888CTR: SHIFTSHIFT20385/22/23Monday5/22/23 8:001/0/00 8:005/22/23 17:001/0/00 17:000.375PLTO: PLANNED TIME OFF08:00:0008:00:00PLTO
11Employee 1Last Name9999BRK: BREAK 1BREAK 1355/22/23Monday5/22/23 10:001/0/00 10:005/22/23 10:151/0/00 10:150.010416667BRK: BREAK 1   
12Employee 1Last Name9999BRK: BREAK 2BREAK 2365/22/23Monday5/22/23 14:151/0/00 14:155/22/23 14:301/0/00 14:300.010416667BRK: BREAK 2   
13Employee 1Last Name9999OP: COACHINGCOACHING575/23/23Tuesday5/23/23 13:001/0/00 13:005/23/23 13:301/0/00 13:300.020833333OP: COACHING   
14Employee 2Last Name8888CTR: SHIFTSHIFT20385/23/23Tuesday5/23/23 8:001/0/00 8:005/23/23 17:001/0/00 17:000.375PLTO: PLANNED TIME OFF08:00:0008:00:00PLTO
15Employee 1Last Name9999BRK: BREAK 2BREAK 2365/23/23Tuesday5/23/23 14:151/0/00 14:155/23/23 14:301/0/00 14:300.010416667BRK: BREAK 2   
16Employee 2Last Name8888BRK: BREAK 2BREAK 2365/23/23Tuesday5/23/23 13:001/0/00 13:005/23/23 13:151/0/00 13:150.010416667BRK: BREAK 2   
17Employee 1Last Name9999WRK: PROGRAM H PROV SVC WIPROGRAM H PROV SVC WI19045/23/23Tuesday5/23/23 8:001/0/00 8:005/23/23 17:001/0/00 17:000.375WRK: PROGRAM H PROV SVC WI   
18Employee 1Last Name9999BRK: BREAK 1BREAK 1355/23/23Tuesday5/23/23 10:001/0/00 10:005/23/23 10:151/0/00 10:150.010416667BRK: BREAK 1   
19Employee 2Last Name8888WRK: PROGRAM H PROV SVC WIPROGRAM H PROV SVC WI19045/23/23Tuesday5/23/23 8:001/0/00 8:005/23/23 17:001/0/00 17:000.375WRK: PROGRAM H PROV SVC WI   
20Employee 1Last Name9999CTR: SHIFTSHIFT20385/23/23Tuesday5/23/23 8:001/0/00 8:005/23/23 17:001/0/00 17:000.375CTR: SHIFT  08:00:00
21Employee 2Last Name8888MEAL: UNPAID MEAL REGULARUNPAID MEAL REGULAR25/23/23Tuesday5/23/23 15:001/0/00 15:005/23/23 16:001/0/00 16:000.041666667MEAL: UNPAID MEAL REGULAR   
22Employee 1Last Name9999MEAL: UNPAID MEAL REGULARUNPAID MEAL REGULAR25/23/23Tuesday5/23/23 11:451/0/00 11:455/23/23 12:451/0/00 12:450.041666667MEAL: UNPAID MEAL REGULAR   
23Employee 2Last Name8888PLTO: PLANNED TIME OFFPLANNED TIME OFF145/23/23Tuesday5/23/23 8:001/0/00 8:005/23/23 17:001/0/00 17:000.375PLTO: PLANNED TIME OFF   
24Employee 2Last Name8888BRK: BREAK 1BREAK 1355/23/23Tuesday5/23/23 10:301/0/00 10:305/23/23 10:451/0/00 10:450.010416667BRK: BREAK 1   
Sheet1
Cell Formulas
RangeFormula
O2:O24O2=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:P24P2=IF(AND(D2="CTR: SHIFT",O2="PLTO: PLANNED TIME OFF"),TIME(HOUR(I2),MINUTE(I2),SECOND(I2)),"")
Q2:Q24Q2=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:R24R2=IF(AND(D2="CTR: SHIFT",O2="PLTO: PLANNED TIME OFF"),"PLTO",IF(D2="CTR: SHIFT",TIME(HOUR(I2),MINUTE(I2),SECOND(I2)),""))
 
Upvote 1
Solution
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!
 
Upvote 0
you are welcome
are you still using 2016 version, s 365 has a lot more functions and may help with converting vertical to horizontal
 
Upvote 0
ok, the functions i used will work with 2016
 
Upvote 1
You must log in or register to reply here.

Similar threads

tezza
  • Solved
Autosum specific rows and columns
Replies
2
Views
274
tezza
tezza
M
  • Question Question
Pivot table not showing as Numbers
Replies
1
Views
288
shift-del
shift-del
U
  • Question Question
VLOOKUP that will SUM data based on Date Range
2
Replies
18
Views
1K
UnitedCloud01
U
P
  • Question Question
Offsetting dates / usage in vlookup or index/match function
Replies
1
Views
557
GlennUK
G
K
  • Question Question
Help with difference between 2 dates including am/pm and check multiple entries to see if they occur at the same date and am/pm
Replies
1
Views
527
XLfriend777
XLfriend777

Forum statistics

Threads
1,224,823
Messages
6,181,180
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top