Macro to enter value based on start date's day of the week

Jibroni

New Member
Joined
Apr 7, 2022
Messages
25
Office Version
  1. 365
  2. 2021
  3. 2019
Platform
  1. Windows
Hello,

I have the following table for which I need a macro that enters a value into column G, SESSION_TEMPLATE, based on the values found in columns B, CLASS_NO and F, START_DATE:

IDCLASS_NOSTATUSCOURSEDOMAINSTART_DATESESSION_TEMPLATE
06052022_GST_40206052022_GST_402
100​
11202VShared
6/5/2022​
07042022_GMT_40107042022_GMT_401
100​
21201VShared
7/4/2022​
07062022_GMT_33007062022_GMT_330
100​
3267​
Shared
7/6/2022​
07072022_MT_30507072022_MT_305
100​
3237​
Shared
7/7/2022​
07112022_SGT_30107112022_SGT_301
100​
6541​
Shared
7/11/2022​
07142022_SGT_30207142022_SGT_302
100​
6545​
Shared
7/14/2022​

The value I need to enter into column G, SESSION_TEMPLATE, is specific to the day of the week found in column F and the last three numbers of the string of characters found in column B. For example, for row 1, I want the value needs to be "1-day Sun" because the 402 (from 06052022_GST_402) = 1-day and the date in the START_DATE column for this row is a Sunday (6/5/2022). Another example, in row 2, I want the value to be "2-day Mon" because 401 = 2-day and the date in START_DATE column for this row is a Monday (7/4/2022).

The rest of the key is 330 = 3-day, 305 = 1-day, 301 = 3-day, and 302 = 1-day.

If you have a formula that does the trick, that works as well.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Bumping this in hopes someone out there might know
 
Upvote 0
If you have a formula that does the trick, that works as well.
Use a lookup table (even the same one?) like I suggested in your other thread, then a formula like this.

Jibroni.xlsm
AFGHIJ
1IDSTART_DATESESSION_TEMPLATECodeDays
206052022_GST_4025/06/20221-Sunday3013
307042022_GMT_4014/07/20222-Monday4012
407062022_GMT_3306/07/20223-Wednesday4021
507072022_MT_3057/07/20221-Thursday3021
607112022_SGT_30111/07/20223-Monday3051
707142022_SGT_30214/07/20221-Thursday3303
Session Template
Cell Formulas
RangeFormula
G2:G7G2=VLOOKUP(RIGHT(A2,3)+0,I$2:J$10,2,0)&TEXT(F2,"-dddd")
 
Upvote 0
Thank you @Peter_SSs. This gives me the result I want. Awesome!

For any cell in column A that has "_GST_", I want the value in column G to say "META" before the "Days-Start Day". Using the first row as an example, the formula would result in the value being "META 1-Sunday". Is there any way to update the formula to do this? Would I add additional codes to the lookup table, that could be something like, "GST_402" = "META 1-Sunday". If so, I need to understand how the formula you shared finds the code from column I. I really appreciate your help.
 
Upvote 0
Try this

Jibroni.xlsm
AFGHIJ
1IDSTART_DATESESSION_TEMPLATECodeDays
206052022_GST_4025/06/2022META 1-Sunday3013
307042022_GMT_4014/07/20222-Monday4012
407062022_GMT_3306/07/20223-Wednesday4021
507072022_MT_3057/07/20221-Thursday3021
607112022_SGT_30111/07/20223-Monday3051
707142022_SGT_30214/07/20221-Thursday3303
Session Template (2)
Cell Formulas
RangeFormula
G2:G7G2=IF(ISNUMBER(FIND("_GST_",A2)),"META ","")&VLOOKUP(RIGHT(A2,3)+0,I$2:J$10,2,0)&TEXT(F2,"-dddd")
 
Upvote 0
Solution
You're welcome. Thanks for the follow-up. :)
 
Upvote 0
Alternative, without using helper columns
Excel Formula:
=IF(ISNUMBER(FIND("_GST_",A2)),"META ","")&LOOKUP(RIGHT(A2,3)+0,{301;302;305;330;401;402},{3;1;1;3;2;1})&TEXT(F2,"-dddd")
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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