﻿ Converting different date formats

# Converting different date formats

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

ANSWERS:

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