Forum Discussion
Translating serial numbers into date codes
Im trying to create a function were as two characters within a serial number are replaced to give me a date code.
For example 7AC3323A is a serial number of a product of one of our machines. The first digit represents the year and the second digit represents the month. So this machine was manufactured on October 2017. Because the A stands for October and the 7 stands for 2017. Since our product is within four years of its lifespan the first digit is always the last digit of the fourth digit of the year. 1 is for 2011 2 is for 2012 3 is for 2013 and so on and so forth. And for the second digit the 1-9 represent January through September and October is A or X - November is B or Y and December is C or Z. So basically I am trying to write a function to read the serial number that would take the alphanumeric characters and translate them into a month and year.
| 7AC3323A |
Hi Ruben,
Please try this formula:
=DATE(201&LEFT(A1,1),
IF(OR(MID(A1,2,1)="A",MID(A1,2,1)="X"),10,
IF(OR(MID(A1,2,1)="B",MID(A1,2,1)="Y"),11,
IF(OR(MID(A1,2,1)="C",MID(A1,2,1)="Z"),12,
IF(ISNUMBER(MID(A1,2,1)+0),MID(A1,2,1),NA())))),1)
9 Replies
Thank you I'm starting to understand more and more....quick question.
The role of the ISNUMBER function comes after the failure of all last logical tests, to check if the second digit is number 0-9.
What does this mean....I dont really understand.
Hi Ruben,
This part of the formula is occupied the month argument of the DATE function:
IF(OR(MID(A1,2,1)="A",MID(A1,2,1)="X"),10,
IF(OR(MID(A1,2,1)="B",MID(A1,2,1)="Y"),11,
IF(OR(MID(A1,2,1)="C",MID(A1,2,1)="Z"),12,
IF(ISNUMBER(MID(A1,2,1)+0),MID(A1,2,1),NA()))))The first line will check if the second digit is either "A" or "X".
If not, the formula will step into the second line to check if the second digit is either "B" or "Y".
If not, the formula will step into the third line to check if the second digit is either "C" or "Z".
If not, here comes the role of ISNUMBER!
The role of ISNUMBER function is just an error handler to check if the result of below part is a number
(MID(A1,2,1)+0
If so, it will apply this:
MID(A1,2,1)
Otherwise, it will return #N/A error by applying this part:
NA()
You may wonder what purpose of +0?
Well, if the MID function returned a number 0-9, the number will be in text format, because the MID is one of the text functions in Excel!
To convert it to a real number so that the ISNUMBER function can read it, you have to hit it with any calculation and at the same time do not affect the value of the number such as +0.
Finally, I would change the formula to make it more generic because the error handler is for the second digit not the for the first. This is done by wrapping the whole formula by the IFERROR function as follows:
=IFERROR(DATE(201&MID(A1,1,1),
IF(OR(MID(A1,2,1)="A",MID(A1,2,1)="X"),10,
IF(OR(MID(A1,2,1)="B",MID(A1,2,1)="Y"),11,
IF(OR(MID(A1,2,1)="C",MID(A1,2,1)="Z"),12,
IF(ISNUMBER(MID(A1,2,1)+0),MID(A1,2,1),NA())))),1),
"The code isn't valid!")In such cases:
- &MC3323A
- MXC3323A
- 7W3323A
The formula will return this meaningful message:
"The code isn't valid!"I hope that makes sense for you
Haytham
Hi Ruben,
Please try this formula:
=DATE(201&LEFT(A1,1),
IF(OR(MID(A1,2,1)="A",MID(A1,2,1)="X"),10,
IF(OR(MID(A1,2,1)="B",MID(A1,2,1)="Y"),11,
IF(OR(MID(A1,2,1)="C",MID(A1,2,1)="Z"),12,
IF(ISNUMBER(MID(A1,2,1)+0),MID(A1,2,1),NA())))),1)Thank you so much it worked.....what would I have to change in the functions language if the serial numbers month and year moved somewhere else within the serial number say for example. C37YC3323A now the year is in the 3rd position and the month is in the fourth position.
As a variant to play with formulas
=IFERROR(DATE(MID(A1,3,1)+2010, HLOOKUP(MID(A1,4,1),{"1","2","3","4","5","6","7","8","9","A","X","B","Y","C","Z";1,2,3,4,5,6,7,8,9,10,10,11,11,12,12},2,FALSE),1), "Wrong S/N")
