Week numbers with a difference

[John Colling]

I'm creating a calender in excel which has the following
format

Month
M T W T F S S WK TW
1 2 3 4 5 6 7 1 40

TW= UK fiscal Tax Week number which starts from 6 April
YY to 5 April YY.

For example Tax week 1 falls within 6 April to 12 April

Is their a formula which I can use that will adjust to
account for 52/53 pay periods depending on the year.

Its the last problem that I have, Many thanks
John
Johndotcollingatsijodotcom

 

[Harlan Grove]

I'm creating a calender in excel which has the following
format

Month
M T W T F S S WK TW
1 2 3 4 5 6 7 1 40

TW= UK fiscal Tax Week number which starts from 6 April
YY to 5 April YY.

For example Tax week 1 falls within 6 April to 12 April

Is their a formula which I can use that will adjust to
account for 52/53 pay periods depending on the year.

....

Unsure, but if you define a name like DateOffset referring to

=DATE(YearEntry,4,6)-DATE(YearEntry,1,1)

then subtract this from your actual dates, the result will be the date
serial number for the corresponding day of the calendar year given by
YearEntry. E.g., for DateEntry 13-Apr-2004 with YearEntry 2004,
DateEntry-DateOffset gives 37994, which is the date serial number for
8-Jan-2004, the 8th day of the year. You could use DateEntry-DateOffset in
Excel's date functions and likely get the results you seek.