Forum Discussion
Solved
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 ...
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")
This one also works thank you very interesting to see how different formulas achieve the same result.