Forum Discussion

RonaldvdH's avatar
RonaldvdH
Post Patron
6 years ago
Solved

Convert date into YEAR-Week

I need to change this formula so that the format will be YYYY-WW THis formula works but the result is 201801 or 201802 so without the '-' between Year and Week   How do i change this formula ?   ...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi RonaldvdH 

    Create a caluclated column

    Column = IF(WEEKNUM([Date])<10,FORMAT([Date],"YYYY-0WW"),FORMAT([Date],"YYYY-WW"))
    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AlB's avatar
AlB
Community Champion
6 years ago

But it won't be a number of course:

Week Number =
CONCATENATE (
    YEAR ( 'Date'[Date] );
    "-"
        & CONCATENATE (
            IF ( WEEKNUM ( 'Date'[Date] ) < 10; "0"; "" );
            WEEKNUM ( 'Date'[Date] )
        )
)

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

  • RonaldvdH's avatar
    RonaldvdH
    Post Patron
    6 years ago

    AlB then how do i fix the issue ?

    Ive altered the formula but, like you said, it returned an error that it can't convert type Tekst to Number.

     

    Week Number =
           INT (
                CONCATENATE (
                          YEAR ( 'Date'[Date] );"-" &
                          CONCATENATE (
                                       IF ( WEEKNUM ( 'Date'[Date] ) < 10; "0"; "" );
                                       WEEKNUM ( 'Date'[Date] )
                                    )
                    )
    )
  • RonaldvdH's avatar
    RonaldvdH
    Post Patron
    6 years ago

    AlB  any ideas ? a different formula is also ok