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 ...

Formulas & Functions

Need Help

Haytham Amairah

Apr 20, 2018

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)

SergeiBaklan

Diamond Contributor

Apr 20, 2018

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")

Ruben pineiro Jr

Copper Contributor

Apr 20, 2018

This one also works thank you very interesting to see how different formulas achieve the same result.

Forum Discussion

Translating serial numbers into date codes