Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
dates converted
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
dates converted
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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
dates converted
On Fri, 18 May 2012 18:12:04 -0700 (PDT), bill k wrote:
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dates converted into text | Excel Programming | |||
Excel converted numbers to dates suddenly in All Sheets!!!! | Excel Discussion (Misc queries) | |||
dd/mm converted to mm/dd | Excel Programming | |||
Answer to 2/29 gets converted to 2/1 | Excel Discussion (Misc queries) | |||
How do I import fractions without data being converted to dates? | Excel Discussion (Misc queries) |