Extracting date from string of numbers

I am creating a sheet to flag up issues with products based on serial numbers, this is to flag up instances where a product has been returned for a fault after a modification has been made.

The issue I have is that the serial number format has changed 3 times!

DB1112506589 – first format (always starts with DB11, the next 2 digits are the year, the next 2 are the week, and the last 4 are the serial number)

DB112506589 – second format (exactly the same as previous format but staring with DB1 instead of DB11 so the whole string is 1 digit less)

T212126589 – third format (same as before except DB1 has been changed to T2, and now the date is indicated by year then month, instead of year then week)


The bit causing me a headache is the fact that they had changed the part of the code that was previously indicated by a week to a month.

What I am hoping to achieve is for us to be able to input any of the 3 types of serial numbers and for excel to automatically extract the data I need into the next cell, so we are essentially extracting the last 6 digits containing the date of production and the serial number, the problem being that for the first 2 types of number we need to convert the week to month (for instance week 03 would translate to month 01 and week 52 to month 12 etc)

So is there a way for excel to work out which type is being input (we can easily identify this from the number of digits in the string) and then convert it accordingly?

It is split into weeks of the year, so 1-52. Week 01-04 would be month 01.

Originally Posted by Ace_XL

try

=IF(LEN(A1)>10,TEXT(MIN(LEFT(RIGHT(A1,6),2)/4,12),"00")&RIGHT(A1,4),RIGHT(A1,6))

Thanks that seems to be what I need, wasn't exactly how I imagined doing it but this method will work better

I may have made a slight error however when I gave you the example numbers, there should actually be an extra 2 digits on the end of the serial number, I have tried and tried but cannot figure out how your formula works to adjust it to take into account these 2 extra numbers! Can you help please?

It would also be beneficial for me to display the 2 digits preceding the month (ie the year) is this possible?

Thanks that works perfectly