Thread: Weeknum help

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.
> >