Forum Discussion

jfernand's avatar
jfernand
Frequent Visitor
9 years ago
Solved

Converting date into YYYYWW

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?

Need Help
  • Chihiro's avatar
    Chihiro
    9 years ago

    I'd do it in this manner.

     

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

8 Replies

  • Sean's avatar
    Sean
    Community Champion
    9 years ago

    jfernand

    This should work... :smileyhappy:

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

     

  • Chihiro's avatar
    Chihiro
    Solution Sage
    9 years ago

    I'd do it in this manner.

     

    Week Number =
INT (
    CONCATENATE (
        YEAR ( 'Calendar'[Date] ),
        FORMAT ( WEEKNUM ('Calendar'[Date] ), "00" ) 
        ) 
    )
      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Let's keep it going! :D

         

        YearMo = YEAR(Calendar(Date)) & RIGHT("0" & WEEK(Calendar(date)), 2)
    • jfernand's avatar
      jfernand
      Frequent Visitor
      9 years ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

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