Sort by week and year

[Guest]

Hi,

I am using below dates and convert them to
year and week using a formula that looks like this
=YEAR(A1)&"/"&WEEKNUM(A1)

5/7/2007 0:00 2007/19
1/9/2007 0:00 2007/2
5/15/2007 0:00 2007/20

The combined year and week is then base for a pivot tabel
where I sort the columns by the "Year/week".
The problem is that week 2 is sorted after week 19.

Is there some way to get the "year/week" to look like this
2007/02 when the week is 1-9?

The reason why I can not sort only by week is that I have dates
from different year. Sorting by week then mix up data from
year 2006 week 2 and year 2007 week 2.

Is there some smart person out there who can help me with
this problem?

Patric

 

[Pete_UK]

Try this:

=YEAR(A1)&"/"&TEXT(WEEKNUM(A1),"00")

This will give you leading zeros if the week number is less than 10.

Hope this helps.

Pete

 

[Guest]

Pete....

It sure helped a lot!!!
Thank you so much

 

[Pete_UK]

Thanks for feeding back.

Pete