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)

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

Forum Discussion

Translating serial numbers into date codes