I need the WEEKNUM command to display 2 digits for the week

[lazzareo]

I have a sheet of data and in 'column Q' I have the following command in each cell (example below for cell Q2):

=WEEKNUM(O2) & TEXT(O2, "-yy")

This displays the week and year of the date in 'column O' i.e. 2-19 or 32-18 etc

I then have a pivot that reports on the number of tickets created by week which looks good but as my data spans 2018 & 2019 it displays the week as a single number for weeks 1-9. This is therefore displaying the weeks out of order rather than chronologically. e.g 20-18, 21-18, 2-18, 2-19, 22-18, 23-18 and so on...

If I could get the WEEKNUM command to include two digits for each week then this would solve the problem because in the scenario above 2-18 would become 02-18 and appear near the start.

Would anyone be able able to suggest anything to resolve this.

Thanks.

 

[Joe4]

Just use the TEXT function, like you are already doing for the second part of your formula, i.e.
=TEXT(WEEKNUM(O2),"00") & TEXT(O2, "-yy")

 

[sandy666]

or

=BASE(WEEKNUM(O2),10,2)&TEXT(O2,"-yy")

 

[AlanY]

try this

=TEXT(WEEKNUM(O2),"00") & TEXT(O2, "-yy")

 

[lazzareo]

Awesome...so simple when you know how...thanks to all the replies