Converting date to Work Week

My company tracks many milestones and project dates by work week. To complicate matters our fiscal year starts in July. Below are sample work weeks that correspond to the given dates.


DATE WW
1/1/2007 ww27'07
6/28/2007 ww52'07
7/3/2007 ww01'08
12/25/2007 ww26'08
1/1/2008 ww27'08
6/25/2008 ww52'08

The WW column was generated by a long and inelegant formula that I wrote. Wanted to see if there was a much simpler way to achieve this. My formula:

="ww"&TEXT((IF((WEEKNUM(A2,2))<27,(WEEKNUM(A2,2))+26,(WEEKNUM(A2,2))-26)),"00")&"'"&TEXT(MID((IF((WEEKNUM(A2,2))<27,(YEAR(A2)),(YEAR(A2))+1)),3,2),"00")


Thanks for the help!
Pat



p.s. This is a great forum.

Originally Posted by pstritt

="ww"&TEXT((IF((WEEKNUM(A2,2))<27,(WEEKNUM(A2,2))+26,(WEEKNUM(A2,2))-26)),"00")&"'"&TEXT(MID((IF((WEEKNUM(A2,2))<27,(YEAR(A2)),(YEAR(A2))+1)),3,2),"00")

Not sure how long you want that formula to last. It will give some odd results round about the end of 2012 (because WEEKNUM function generates a week 54). Also you say that your fiscal year starts in July but that formula will give week 1 starting in June in some years. Perhaps you can define when week 1 should start exactly?