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?
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?