Converting different date formats

Question

This is continuation of my other question How to convert Quarter years to other format

Ok so now I need to convert some more stuff :)

I need to convert YYYY-MM to YYWW ex. 2022-10 to 2241 // week 41 because that is halfway through 0ctober.

I also need w.YYWW to become just YYWW and v.YYWW to YYWW which is week in swedish (vecka)

And last but not least I need it to have the convertion from my previous question which was YYYY-Quarter(1234) to YYWW This is the code I used for that

=1*IF(ISNUMBER(-A1),A1,MID(A1,3,2) & CHOOSE(RIGHT(A1,1),"08",20,33,46))

Best case scenario would be if 1 formula could convert all of these into YYWW because I am referencing that cell for my timeline to work.¨

Week of month to take. I just took the week of the day 15 in every month. You can just take them 4 weeks apart or whatever method u might use. The exact week or date is not needed just take something close to the middle of the month.

1 january 02

2 February 07

3 March 11

4 April 15

5 May 19

6 June 24

7 July 28

8 Aug 33

9 Sep 37

10 Oct 41

11 Nov 45

12 Dec 49

ex. 2019-05 becomes 1919

2023-11 becomes 2345

2016-08 becomes 1633

so your going to need a bunch more information before anyone is likely to answer... like which weeks match which months... some examples. — Bryan Davies, Jul 04 '16 at 12:58
There is all the information needed regarding quarters in the other post. For months just take the halfway mark of the month and take the week of that date I can add that to the post if u want. — Felix Torssell, Jul 04 '16 at 13:11
so in other words, you want us to code for you. try these formulas `=left()` `=right()` `=find()` — Bryan Davies, Jul 04 '16 at 13:16
I want help with coming up with methods how to do this efficiently and what formulas to use. Now I need 1 formula to be able to convert multiple types of date formats and I dont really know how to do it. — Felix Torssell, Jul 04 '16 at 13:19
Well `w.YYMM to become just YYMM ` is just `=right(A1,4)` so give it a go for the rest — Bryan Davies, Jul 04 '16 at 13:20
Ok so YYMM is the only one that counts as a number so I've got this to check if its in the wrong format `IF(ISNUMBER(-A1);A1;convert...` — Felix Torssell, Jul 04 '16 at 13:27
you keep saying YYMM to YYMM.... I don't understand, are you changing anything? — Bryan Davies, Jul 04 '16 at 13:30
Is there an if(starts with "w"; do this) formula in excel? 2) No its if there is w.YYMM that I want it to change. If its already YYMM as it can be then I dont want it to change — Felix Torssell, Jul 04 '16 at 13:31
`=IF(FIND("w",C23)=1,TRUE,FALSE)` Or rather this is probably what you want `=IF(FIND("w",C23)=1,RIGHT(C23,4),C23)` — Bryan Davies, Jul 04 '16 at 13:32
Yeah that is what I did but I added "w." for it to work and it worked but then I added "v." afterwards like this `IF(FIND("w.";'Gulpilspuls NT'!T5)=1;RIGHT('Gulpilspuls NT'!T5;4);IF(FIND("v.";'Gulpilspuls NT'!T5)=1;RIGHT('Gulpilspuls NT'!T5;4))))` but then it gave me error #VALUE! — Felix Torssell, Jul 04 '16 at 13:40
alright, your problem is error testing. If find cannot find a "w." then if produces a #Value error. you need to put in some `if(iserror(T5),true,false)` — Bryan Davies, Jul 04 '16 at 13:44
Ok so when I put just =IF(FIND("w.",C23)=1,RIGHT(C23,4),C23) or =IF(FIND("v.",C23)=1,RIGHT(C23,4),C23) It works but when I combine them both it gives me the value error — Felix Torssell, Jul 04 '16 at 13:46
Well if you play around with the order, you can get something far better: `=IF(ISERROR(OR(FIND("w",C23),FIND("V",C23))),RIGHT(C23,4),C23)` — Bryan Davies, Jul 04 '16 at 13:50
Yeah that worked like a charm. Now I am trying to add the next ones so this is how I continued and it gave me value error `=1*IF(ISNUMBER(-'Gulpilspuls NT'!T5);'Gulpilspuls NT'!T5;IF(ISERROR(OR(FIND("w.";'Gulpilspuls NT'!T5);FIND("v.";'Gulpilspuls NT'!T5)));RIGHT('Gulpilspuls NT'!T5;4);IF(FIND("-Q";'Gulpilspuls NT'!T5);MID('Gulpilspuls NT'!T5;3;2)&CHOOSE(RIGHT('Gulpilspuls NT'!T5;1);"08";20;33;46))))` But I found something interesting. If I remove 1* in the beginning ,which I have to make it numeric, I get the result 6-Q2 when I put 2016-Q2 in the cell that I want to convert — Felix Torssell, Jul 04 '16 at 14:08
alright, please change the list up the top for everything you want to change... because you seem to want to change something to text `2016-Q1` but have a `1*` and an isnumber?? — Bryan Davies, Jul 04 '16 at 14:16
I do not want to change anything to text. I want to change from text hehe. - From w.YYWW(text because of the "w.") to just YYWW (Numeric) - From v.YYWW (text because of the "v.") to just YYWW (Numeric) - From YYYY.MM (Text because of the ".") to YYWW (Numeric) - From YYYY-Q1-4 (text because of "-Q") to YYWW (Numeric) — Felix Torssell, Jul 04 '16 at 14:21
ok.... dores my formula from the previous question work how you want? — Bryan Davies, Jul 04 '16 at 14:30
anyway, i gotta go to bed. maybe next time you might reward people with rep if they help you for hours. — Bryan Davies, Jul 04 '16 at 15:38
Sorry mate, I am new to stackoverflow and dont know how to give rep. I am not sure that I can since this is a fairly new acc and I need to unlock some functions still — Felix Torssell, Jul 05 '16 at 06:46
anyway, lets have a look at this. We just need YYYY.MM to change and the YYYY-QQ-4 to change. Can you give me a list of MM to WW results you want and the same with the Q1-4 part (still don't understand what the 4 is for??) — Bryan Davies, Jul 05 '16 at 08:26
ok so YYYY-Q1 to YY08 ,YYYY-Q2 to YY20 ,YYYY-Q3 to YY33 ,YYYY-Q4 to YY46 I have the YYYY.MM thing in the bottom of my question — Felix Torssell, Jul 05 '16 at 08:39

score 0 · Accepted Answer · answered Jul 05 '16 at 13:25

Alright for w.YYMM to YYMM and v.YYMM to YYMM the same formula of right(D8,4) #1 will work.

YYMM #2 doesn't need to change

YYYY-QQ requires =MID(D8,3,2) & CHOOSE(RIGHT(D8,1),"08",20,33,46) #3

And YYYY.MM requires =MID(D8,3,2)&CHOOSE(RIGHT(D8,2),"02","07",11,15,19,24,28,33,37,41,45,49) #4

So all we need now is to combine formulas and choose between them.

Easiest to choose is by length... which also minimizes the nesting.

so =IF(LEN(D8)=4,"#2",IF(LEN(D8)=6,"#1",IF(MID(D8,5,1)="-","#3","#4")))

or all combined, with the 1* on the front:

=1*IF(LEN(D8)=4,D8,IF(LEN(D8)=6,RIGHT(D8,4),IF(MID(D8,5,1)="-",MID(D8,3,2) & CHOOSE(RIGHT(D8,1),"08",20,33,46),MID(D8,3,2)&CHOOSE(RIGHT(D8,2),"02","07",11,15,19,24,28,33,37,41,45,49))))

Next time.... I think I let you do it yourself. I have provided the technique of getting there, you now need to use the evaluate formula and google to see what I did and how I did it

Thank you so much it worked like a charm. I might add that I have been working on combining them myself for 4 hours today but couldnt figure out how when I used Search formula to figure out which method of convertion that was needed. I see now that LEN was a lot easier but didnt know that it even existed. Thank you for taking the time, it is greatly appreciated. — Felix Torssell, Jul 05 '16 at 13:46
Now if you could solve mine... :) http://stackoverflow.com/questions/38168722/excel-vba-controls-webpage-and-fills-out-details-clicks-on-save-appears-to-wor — Bryan Davies, Jul 05 '16 at 14:02

Converting different date formats

1 Answers1