I am having trouble calculating the fiscal weeks of the current year. In my
company's calendar, FW1 starts on Monday, January 2, 2006. I found the
weeknum function and I keep having it tell me that FW 1 is on 1/1/06. Does
anyone else know of other functions I can use or do I have to do some
programming to get this straightened out?
Added bonus question: If I dynamically link a cell that contains a date
from MS Project to Excel, how can I format the cell in Excel to display the
FW not the date.

Any help with either of the questions would be greatly appreciated.

If FW1 begins on 1/2/06, then you could calculate a given date's FW as
=INT((A1+7-DATE(2006,1,2))/7) (with the chosen date in A1)
Alternately, with a weeknum in A1, that week begins on
=date(2006,1,2)+7*(a1-1) (be sure to format the result as a date).


"SoSoExcelGuy" wrote:

I am having trouble calculating the fiscal weeks of the current year. In my
company's calendar, FW1 starts on Monday, January 2, 2006. I found the
weeknum function and I keep having it tell me that FW 1 is on 1/1/06. Does
anyone else know of other functions I can use or do I have to do some
programming to get this straightened out?
Added bonus question: If I dynamically link a cell that contains a date
from MS Project to Excel, how can I format the cell in Excel to display the
FW not the date.

Any help with either of the questions would be greatly appreciated.

Thanks a million for the help

"bpeltzer" wrote:

If FW1 begins on 1/2/06, then you could calculate a given date's FW as
=INT((A1+7-DATE(2006,1,2))/7) (with the chosen date in A1)
Alternately, with a weeknum in A1, that week begins on
=date(2006,1,2)+7*(a1-1) (be sure to format the result as a date).


"SoSoExcelGuy" wrote:

I am having trouble calculating the fiscal weeks of the current year. In my
company's calendar, FW1 starts on Monday, January 2, 2006. I found the
weeknum function and I keep having it tell me that FW 1 is on 1/1/06. Does
anyone else know of other functions I can use or do I have to do some
programming to get this straightened out?
Added bonus question: If I dynamically link a cell that contains a date
from MS Project to Excel, how can I format the cell in Excel to display the
FW not the date.

Any help with either of the questions would be greatly appreciated.