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



 MORE:


 ? Knockout click binding to checkbox prevents jQuery control of value
 ? Fill select on click with Knockout
 ? Download data from webpage using vba Excel
 ? Webpage login not working Using excel VBA
 ? knockout js: data-bind click not working
 ? How to keep html DOM elements synchronized with the associated javascript object? Best practices
 ? How to keep html DOM elements synchronized with the associated javascript object? Best practices
 ? How to keep html DOM elements synchronized with the associated javascript object? Best practices
 ? How to keep html DOM elements synchronized with the associated javascript object? Best practices
 ? Value of `this` in a function called from an event handler callback?