Reformat text into date?

vinvinvin123

New Member
Joined
Jul 19, 2017
Messages
16
Hello,

My column J contains a list of dates / week numbers in the following formats:

week YYYY, week number
week YYYY, week number / week number (range is always 4 weeks)

examples

week 2017 42
week 2018 04/08

What I am trying to do is to reformat the information into MM/YY format. I am not interested in the DD being correct as long as it falls within the correct month - so in effect I am looking for XX/MM/YY format with XX being any day in the month.

For the cells with a range of week numbers I would like the date to be the latest date in the range. ( e.g. week 2018 04/08 would be 02/2017 )

So far I have extracted the year into one column using this formula:

=IF((LEFT(J170,6))=" Stock","stock",IF((LEFT(J170,9))="week 2017",2017,IF((LEFT(J170,9))="week 2018",2018,0)))

My plan was to use a similar method to use the last two digits to identify the month, use a third column to combine with DATE(YYYY,MM,01). I was going to extract the month with a nested IF but started writing the formula and realised it will be very long winded as I would need 52 alternatives ( one for each week num! ).

Is there a better way to do this?
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
@vinvinvin123

1. First select the range (or entire column) where the formula will go, right-click, and choose Format Cells. Choose Custom (bottom of the list) and in the field under "Type:" enter mm/yyyy.

2. Assuming your data begins in J2, enter the following formula into K2 and drag-copy down as far as needed:

=DATE(VALUE(MID(J2,FIND(" ",J2)+1,4)),1,1)+((RIGHT(J2,2)-1)*7)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top