Alpha-Numeric serial numbers to dates

New Contributor



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


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.

4 Replies
best response confirmed by MichaelFoster (New Contributor)


With a serial number in A1:



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?


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.

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