Translating serial numbers into date codes

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