Translating serial numbers into date codes

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.
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
  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.
  - Haytham Amairah
    Silver Contributor
    Apr 20, 2018
    Hi Ruben,
    
    You can translate https://support.office.com/en-us/article/or-function-7d17ad14-8700-4281-b308-00b131e22af0 to Either!
    For example, you can translate this part of the formula:
    IF(OR(MID(A1,2,1)="A",MID(A1,2,1)="X"),10
    To this:
    "If the second digit of the text in cell A1 is either equal to "A", or equal to "X", then please show me the number of 10 in the year argument of the https://support.office.com/en-us/article/DATE-function-E36C0C8C-4104-49DA-AB83-82328B832349."
    
    The role of the https://support.office.com/en-us/article/is-functions-0f2d7971-6019-40a0-a171-f2d869135665?ui=en-US&rs=en-US&ad=US comes after the failure of all last logical tests, to check if the second digit is number 0-9.
    If so, it will leave it as it is to represent the month, otherwise, the formula will return #N/A error.
    
    NA() comes finally to tell you that the second digit of the code isn't equal to A, X, B, Y, C, Z, or 0-9.
    
    The last four right brackets are used to close IF functions.
    
    Also, the very last part of the formula represents the day argument of the DATE function.
    1)
    
    Hope that makes sense

Forum Discussion

Translating serial numbers into date codes

Resources