Decoding a serial number's data

[nmgmarques]

So this one will probably wreak havoc on some less experienced brains. At least it is on mine.

So take the serial number 1284300249. This serial number shows me all I need to know of a products origin. The serial number gets broken down to 4 parts
12
8
43
00249

Using MID, LEFT and RIGHT I was able to isolate these values from cell B1 into their respective cells (B3 to B6), so right now I have something like this on Sheet1 a.k.a. Serial Number Decoder:

The goal now is to have the relevant data show in C. The company should be easy enough. The first 2 digits vary from 10 to 29 where each number is it's own country. Say 10 for US, 11 for Mexico, 12 for Canada, 13 for Brasil, etc... So 12, we would know the country of origin is Canada. A MATCH INDEX should solve this one, by matching value from B3 to an array in Sheet 2 Column A and displaying the countries listed in corresponding Sheet 2 Column B.

Now this is where things get dicey.

The third digit represents the range of years between which the part was manufactured. Starting back in 1976, the number grows by 1 each 5 years. So 1976 to 1980 = 1, 1981 to 1985 = 2, ... , 2006 to 2010 = 7 and 2011 to 2016 = 8.

I can get the range by doing 8*5+1976 = 2016 and then some extra wizardry to display "Between 2011 and 2016".

But to get the correct year, we have to look at the 43. So this number increments 12 steps for each year. 01 Would be January, first year of range 2011-2016, so basically January 2011. 09 would be September, first year meaning September 2011. 12 is December of first year; December 2011. Then we'd jump from 12 to 21. January of second year or January 2012, 32 would be December second year or December 2012. Jump to 41 January 2013. And our 43 being March 2013.
Recapping:
01-12 - 2011
21-32 - 2012
41-52 - 2013
61-72 - 2014
81-92 - 2015

This is where it's mind twisting for me. I can't think of a way to get Excel to display the year of manufacture and month of manufacture based on this value. Any and all help much, much appreciated.

 

[NBVC]

Assuming the range for second digit being 8 is 2011 - 2015 (not 2016) i.e. 5 years.

and assuming the result for the 2nd digit is located in B2 (i.e. for this case B2 shows 2015) and assuming the 43 is in A3, then try:

=DATE(B2-4+LOOKUP(A3,{1,21,41,61,81},{0,1,2,3,4,5}),A3-LOOKUP(A3,{1,21,41,61,81})+1,1)

or replace B2 in formula with 1975+5*A2

 

[nmgmarques]

Assuming the range for second digit being 8 is 2011 - 2015 (not 2016) i.e. 5 years.

and assuming the result for the 2nd digit is located in B2 (i.e. for this case B2 shows 2015) and assuming the 43 is in A3, then try:

=DATE(B2-4+LOOKUP(A3,{1,21,41,61,81},{0,1,2,3,4,5}),A3-LOOKUP(A3,{1,21,41,61,81})+1,1)

or replace B2 in formula with 1975+5*A2

Thanks, the formula works but I have run into a snafu of sorts. If I create a worksheet that has those values in the cells you mention, that works like a charm. When I copy the formula over to my Worksheet and adjust the references to the cells in question, it errors out. It seems that when I use MID, LEFT and RIGHT to extract relevant portions, the displayed result isn't a number but rather a text string. If I manually type over the values shown in the cells, then it works. Is there a way to use MID, LEFT and RIGHT and have the results be numbers?

EDIT:
DOH!!!! Nevermind. Forgot I can use =VALUE(MID(.....)) and such.

 

[NBVC]

Yes to convert to numbers add +0 to the formulas.

e.g.

=LEFT(A1,2)+0

=MID(A1,3,5)+0

=RIGHT(A1,1)+0

 

[nmgmarques]

Yes to convert to numbers add +0 to the formulas.

e.g.

=LEFT(A1,2)+0

=MID(A1,3,5)+0

=RIGHT(A1,1)+0

Solved it as you can see by my edit. But thanks all the same. Your previous formula seems to have hit this thing on the nose. Bang up job! Kudos and +1. You, sir, have won today's internets!