Alpha-Numeric serial numbers to dates

[MichaelFoster]

Hello,

I am looking to make a formula to convert a serial number, for example

AC21BDA123

into a date (month and year). 

The above serial number would be year as the 1st and 2nd number (2021).

The letter after the year would be the month (A = Jan, B = Feb, etc.)

Is it possible to use a single formula to convert these? If it could be shown how to create such a formula it would be very helpful.

Thanks for the help.

[#2 · Accepted solution]

@MichaelFoster 

With a serial number in A1:

=DATE(MID(A1,3,2)+2000,CODE(MID(A1,5,1))-64,1)

[#3]

This works, thank you! I see how we get the year by using the two numbers with the date function, but how do you use the code function to get the month?

[#4]

@MichaelFoster 

CODE returns the ASCII/ANSI code of a character.

The ASCII code of "A" is 65, that of "B" is 66 etc.

If we subtract 64 from that, we get the month number 1, 2, etc.

[#5]

That's a creative way I didn't think about when trying to get the month to work, thank you for showing me!