dates converted

[bill k]

I need to find a formula to change a production date into a 2 letter
cypher. The months are for instance aug = A, sept= B, oct = C, etc
the years are 2012 = W, 2013=X, 2014=Y, 2015=Z, 2016=A
So a complte date is described as JW18 for 18 may 2012

 

[Claus Busch]

Hi Bill,

Am Fri, 18 May 2012 18:12:04 -0700 (PDT) schrieb bill k:

I need to find a formula to change a production date into a 2 letter
cypher. The months are for instance aug = A, sept= B, oct = C, etc
the years are 2012 = W, 2013=X, 2014=Y, 2015=Z, 2016=A
So a complte date is described as JW18 for 18 may 2012

write for example in H1 8 (for aug) and in I1 A, H2 = 9, I2 =B and so on
for each month. Then in K1 2012 and in L1 W, K2=2013, L2 = X

Your date (18 may 2012) is in A1. Then try:
=VLOOKUP(MONTH(A1),$H$1:$I$12,2,0)&VLOOKUP(YEAR(A1),$K$1:$L$5,2,0)&DAY(A1)


Regards
Claus Busch

 

[Ron Rosenfeld]

I need to find a formula to change a production date into a 2 letter
cypher. The months are for instance aug = A, sept= B, oct = C, etc
the years are 2012 = W, 2013=X, 2014=Y, 2015=Z, 2016=A
So a complte date is described as JW18 for 18 may 2012

If the day of the month will always be a two digit number, then try:

=CHAR(MOD(MONTH(A1)-8,12)+65) &
CHAR(MOD(YEAR(A1)-2016,26)+65) &
TEXT(DAY(A1),"00")

If the day of the month can be a single digit, then try:

=CHAR(MOD(MONTH(A1)-8,12)+65) &
CHAR(MOD(YEAR(A1)-2016,26)+65) &
DAY(A1)