yyyyww formula

[drom]

Hi and Thanks in advance

I have a Column with DATES

I need a colum with YYYY-WW, check Column Expected

To get the correct WeekNumber we have to use =WEEKNUM(DATE;21)

But accodng to this the Weeknumber the Year has to be modified manually (because I do not know how to do this by formula)

Have a look at this

The real Sheet is gonna have only 2 Columns DATE and Formula

DAY	MONTH	YEAR	DATE	WeekNumber WEEKNUM(F14;$21)	Expected (YYYY-WW)	Formula??
01	01	2019	2019-Jan-01 Tuesday	01	2019-01
02	01	2019	2019-Jan-02 Wednesday	01	2019-01
03	01	2019	2019-Jan-03 Thursday	01	2019-01
04	01	2019	2019-Jan-04 Friday	01	2019-01
05	01	2019	2019-Jan-05 Saturday	01	2019-01
06	01	2019	2019-Jan-06 Sunday	01	2019-01
29	12	2019	2019-Dec-29 Sunday	52	2019-52
30	12	2019	2019-Dec-30 Monday	01	2020-01
31	12	2019	2019-Dec-31 Tuesday	01	2020-01
01	01	2020	2020-Jan-01 Wednesday	01	2020-01
02	01	2020	2020-Jan-02 Thursday	01	2020-01
29	12	2020	2020-Dec-29 Tuesday	53	2020-53
30	12	2020	2020-Dec-30 Wednesday	53	2020-53
31	12	2020	2020-Dec-31 Thursday	53	2020-53
01	01	2021	2021-Jan-01 Friday	53	2020-53
02	01	2021	2021-Jan-02 Saturday	53	2020-53
03	01	2021	2021-Jan-03 Sunday	53	2020-53
29	12	2021	2021-Dec-29 Wednesday	52	2021-52
30	12	2021	2021-Dec-30 Thursday	52	2021-52
31	12	2021	2021-Dec-31 Friday	52	2022-01
01	01	2022	2022-Jan-01 Saturday	52	2022-01
02	01	2022	2022-Jan-02 Sunday	52	2022-01
03	01	2022	2022-Jan-03 Monday	01	2022-01

Any help ?

 

[Joe4]

I do not understand what the question is, or what you mean by this:

But accodng to this the Weeknumber the Year has to be modified manually (because I do not know how to do this by formula)

According to what?
Why does it need to be modified?
And how exactly does it need to be modified (what should it be showing that it is not)?

 

[drom]

I need a Formula to get Something similar to the Column F (Expected with the Formatting (yyyy-WW, yyyy= year with 4 number, ww, Week with 2 numbers formatting)
The Column F does no have a formula, but this is what I need
If the Date is in column D and the 1st of Janyuary 2021, WeekNumber is 53 (Using the Formula WeekNumber (2021-12-31,21))
I cant put in Column Formula the Following Formula:
=TEXT(YEAR(D2);"0000") & "-" & TEXT(WEEKNUM(D2;21);"00")

• because I will get = 2021-53 ( year = 2021, WeekNumber = 53)

because this is Wrong
I need fo this date the answer = 2020-53

Column Expected is what I am trying to get

• Where: in Column Formula

and The WeekNumber needs 21 as the second parameter, (return type)

 

[drom]

The weekNumber we use in our company can be obtained using
WeekNumber (2021-01-01, 21)
But when the First of january has 53 as the WeekNumber, this date belong to the prior year, So 2020-53
And if the 30th of December has as weeknumber 01 (eg: 30-12-2019) thw YYYY-WW I need a formula to get 2020-01
The The WeekNumber is easy, is DONE, but correct the year for those dates where the WeekNumber 01 has as month 12 or the the dates where the WeekNumer is 53 and the month 01 ??

 

[KenU]

If I have understood correctly, it appears that your expected result column has three erroneous values near the bottom (12/31/2021 - 1/2/2022). If that is true, then this formula should work for dates beginning in D3.

=((MONTH(D3)=12)*(WEEKNUM(D3,21)=1))+((MONTH(D3)=1)*(WEEKNUM(D3,21)>=52)*-1)+YEAR(D3) & TEXT(WEEKNUM(D3,21),"-00")

Hope that helps.

Regards,
Ken

 

[Tetra201]

Here is a shorter formula for you to consider:

=YEAR(D3-WEEKDAY(D3,3)+3)&TEXT(WEEKNUM(D3,21),"-00")