Forum Discussion

Ruben pineiro Jr's avatar
Ruben pineiro Jr
Copper Contributor
Apr 20, 2018
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 ...
Formulas & Functions
Need Help
  • Haytham Amairah's avatar
    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)

     

Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
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)

 

  • Ruben pineiro Jr's avatar
    Ruben pineiro Jr
    Copper Contributor
    Apr 20, 2018

    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.

     

    • SergeiBaklan's avatar
      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's avatar
        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.

    • Haytham Amairah's avatar
      Haytham Amairah
      Silver Contributor
      Apr 20, 2018

      Hi Ruben,

       

      You just have to do the following changes:

       

      Replace this function:

      LEFT(A1,1)

      With this:

      MID(A1,3,1)

      This for the year, it now starts from the third digit, instead of the first.

       

      For the month, you have to change all MID functions to start from the fourth digit instead of the second, so you have to replace this:

      MID(A1,2,1)

      With this:

      MID(A1,4,1)

       

      This is the whole formula:

      =DATE(201&MID(A1,3,1),
            IF(OR(MID(A1,4,1)="A",MID(A1,4,1)="X"),10,
            IF(OR(MID(A1,4,1)="B",MID(A1,4,1)="Y"),11,
            IF(OR(MID(A1,4,1)="C",MID(A1,4,1)="Z"),12,
            IF(ISNUMBER(MID(A1,4,1)+0),MID(A1,4,1),NA())))),1)

       

      Hope that helps

      • Ruben pineiro Jr's avatar
        Ruben pineiro Jr
        Copper Contributor
        Apr 20, 2018

        It worked thank you so much but I've come to realize that I need more training on the fundamentals. Such as , how does the OR function work, how does the ISNUMBER function work, and why is the NA ())))) at the end of this function. I know to you these might seem like simple answers but for me unless I can understand the answers I wont be able to write my own functions.

Resources