Formula for Date from text

**gautamacharya** · 03-22-2012, 11:15 AM

Hi,
I have a text filed that has Serial Numbers like "EBT0817J434" the 08 is the year and the 17 is the week of the year. I need the formula to return in a dd/mm/yyyy format. I have a formula (someone in this forum had helped me

) to extract the mm/yyyy from the same field but it did not extract the day. Below is that formula. If you can add the beginning of the week for the day to this formula, then great.

Please Login or Register  to view this content.

Thanks
Gautam

**Dennis7849** · 03-22-2012, 11:32 AM

I would use a custom function for this.

Please Login or Register  to view this content.

If the encoded date is in cell A1, then in cell B1 you enter the formula =GetDate(A1)

**gautamacharya** · 03-22-2012, 11:44 AM

Thanks Dennis. I have a few more VB codes in the sheet and don't know how to add this code in. I tried but it gives a #NAME? error in excel.
Could you possibly give the same formula in Excel instead of a VB Function?
Thanks
Gautam

**daddylonglegs** · 03-22-2012, 11:47 AM

I understand that you are using ISO week numbers, in which case you could use this formula to give the start date (Monday) of the week

=DATE(100+MID(A1,4,2),1,MID(A1,6,2)*7-2)-WEEKDAY(DATE(100+MID(A1,4,2),1,3))