YearWeeknum formula

[Akakaboto]

2016-02-21 00:00:00
2015-05-01 00:00:00

In my Powerpivot model I have a dimdate table with lots of different date formats. There is however one format missing that I would like to add as an extra column, namely YearWeeknum.


I want first to be written as "2015 w.08" and second as "2015 w.18"

So for all weeks<10 I want to add a "0" before the weeknumber to avoid having 2015 w.19, 2015 w.2, 2015 w.20, 2015 w.21....etc in my graphs.

=YEAR([FullDate])&" w."&WEEKNUM([FullDate];2) would work if i didn't care about the extra 0 for weeknum<10 but when I try to account for that the formula does not work, see below.

=YEAR([FullDate])&" w."&IF(WEEKNUM([FullDate];2)>10;"0"&WEEKNUM([FullDate];2);WEEKNUM([FullDate];2))

What is wrong in my formula above?

 

[Akakaboto]

=YEAR([FullDate])&" w."&IF(WEEKNUM([FullDate];2)>10;"0"&WEEKNUM([FullDate];2);WEEKNUM([FullDate];2))

Got this formula wrong when entering it here...but still not working

=YEAR([FullDate])&" w."&IF(WEEKNUM([FullDate];2)<10;"0"&WEEKNUM([FullDate];2);WEEKNUM([FullDate];2))

 

[Ozeroth]

I would use the FORMAT function to ensure the weeknum is always two digits.

This works for me:

=YEAR( [FullDate] ) & " w." & FORMAT( WEEKNUM( [FullDate]; 2 ); "00" )

 

[Akakaboto]

I would use the FORMAT function to ensure the weeknum is always two digits.

This works for me:

=YEAR( [FullDate] ) & " w." & FORMAT( WEEKNUM( [FullDate]; 2 ); "00" )

Thank you Ozeroth! It works perfectly. You just saved my day!