mm/yy out of a serial number

[gautamacharya]

I have a serial no of a product for in cell A1. EBT0806S870. Out of this, the 4th letter from the left is the beginning of the date code- 0806. The first two letter are the year 2008 and the next two are the week-06. I would like to extract the month out of the week. I would like to output to be 08/02 (yy/mm)so that i can pivot this data elsewhere.

Thanks for your help.

[arthurbr]

Hi and welcome to the board

How do you define week1 ? ISO ? 1st Monday of year ? Week containing Jan 1st?

[gautamacharya]

Hi Arthur-
ISO is the defn we are using.

Thanks
Gautam

[watersev]

hi, please check attachment

[gautamacharya]

Fantastic watersev. It works like magic. Thanks a lot.

[gautamacharya]

Watersev,
Also any idea how to remove the day from this? When i pivot it, the dropdown still has the mm/dd/yyyy format. I would like to see only the yy/mm format in the dropdown. Possibly make this a text? Please help.

Thanks
Gautam

[watersev]

in order to make it yy/mm format I used custom date format yy/mm (select range - right mouse click - format - all and write yy/mm) or check the format in posted file. Hope this helps

[gautamacharya]

Thanks again !!

[gautamacharya]

Hey I tried that, id does not work. It works ok for seeing the values in the sheet. But when you pivot it in a pivot table, and drop down to select multiple cells, it shows the entire mm/dd/yyyy. I have attached the spreadsheet. Please look. So maybe if you can get the output to be the first of the month instead of the exact day, it will work.

[gautamacharya]

I got a formula for that it is, =DATEVALUE(MONTH(A1)&"/"&YEAR(A1)). Can you combine this formula with the one you have.=DATE("20"&LEFT(MID(A2,4,4),2),1,1)+RIGHT(MID(A2,4,4),2)*7