Converting date into YYYYWW

[jfernand]

Hi team,

I have a date in the 2017-01-01 format. I wanted to convert it into 201701 (week number 1 of the year 2017)

I have used the formula

Week Number = YEAR( Calendar(date) ) & WEEK( Calendar(date) )

and the result I get is 20171

I need it to be exactly 201701. How should I procceed?

[Chihiro · Accepted solution]

I'd do it in this manner.

Week Number =
INT (
    CONCATENATE (
        YEAR ( 'Calendar'[Date] ),
        FORMAT ( WEEKNUM ('Calendar'[Date] ), "00" ) 
    ) 
)

[jfernand]

wow, apparently there were tons of options for this scenario. Thank you everyone for your help. This option ended up being the most complete and yet simple

[konstantinos]

@Sean @jfernand  How about 

WeekNum =
 ( YEAR ( Calendar[date] ) * 100 )
    + WEEKNUM ( Calendar[date] )

[Anonymous]

Let's keep it going! 😄

YearMo = YEAR(Calendar(Date)) & RIGHT("0" & WEEK(Calendar(date)), 2)

[Sean]

@konstantinosIts good to see you back on here

@ChihiroI should have thought of that shortcut - I like it

I actually use something similar for a Month Order Column

Month Order = VALUE ( FORMAT ( 'Calendar'[Date], "YYYYMM" ) )

@AnonymousI believe you were in a hurry to post...

Week Number =
INT (
    YEAR ( 'Calendar'[Date] )
        & RIGHT ( "0" & WEEKNUM ( 'Calendar'[Date] ), 2 )
)

 @jfernand Take your pick!

[Anonymous]

Here is a simplier one

Week Number = YEAR(Calendar(date)) & FORMAT(WEEK(Calendar(date)), "00")

[Anonymous]

=FORMAT(Calendar[Date], "YYYYMM")

[Sean]

@jfernand

This should work...

Week Number = 
    INT (
        CONCATENATE (
            YEAR ( 'Calendar'[Date] ),
            CONCATENATE (
                IF ( WEEKNUM ( 'Calendar'[Date] ) < 10, "0", "" ),
                WEEKNUM ( 'Calendar'[Date] )
            )
        )
    )