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( Cal...
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" ) 
        ) 
    )
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" ) 
    ) 
)
  • konstantinos's avatar
    konstantinos
    Memorable Member
    9 years ago

    Sean jfernand  How about 

    WeekNum =
 ( YEAR ( Calendar[date] ) * 100 )
    + WEEKNUM ( Calendar[date] )
    • 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)
      • Sean's avatar
        Sean
        Community Champion
        9 years ago

        konstantinosIts good to see you back on here :smileyhappy:

        ChihiroI should have thought of that shortcut - I like it :smileyhappy:

        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... :smileyhappy:

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

         jfernand Take your pick! :smileyhappy:

  • 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