Solved: Fiscal Week Calculations...

[asingh]

Hi,

I am making a spreadsheet which will be dependant on the Fiscal Week.

Is it possible that a user selects a number from a data validation list [1 ---- 52].....and the start date and beginning date of the Fiscal week..as per the user choice is displayed in two adjacent cells.

So if the user selects "3"...the cells next to it..display the start date/end date of Fiscal week 3 ---2006......?

I dont want to use VBA for this..so the solution should be using standard Excel formual functions...!

If not possible then I will have to make a V--lookup table and pick the start/end dates of each Fiscal week from there..the problem with this is ..that I will have to create a new Fiscal week, V -- lookup table at the beginning of each year... :(

thanks and regards,

asingh

[mdmackillop]

With a couple of range names you could use
=Start06+7*Week and =Start06+6+7*Week
where Start06 is the date of the start of the 06 fiscal year and Week is your 1 - 52 selection

[Bob Phillips]

Are you using MS week numbers or ISO week numbers, and when does your year start?

[asingh]

XLD...what do you mean by MS weak numbers or ISO weak numbers...my year would start at Jan.1st 2006....

[mdmackillop]

Hi asingh
FYI Fiscal year in UK starts 1 April

[asingh]

No No...not that at all..Fiscal Year would begin..on the first monday....of January...?

[mdmackillop]

That is why XLD's clarification is required.

[Bob Phillips]

There is a difference in the way that MS determine where week 1 starts and where ISO says it starts.

Excel can have a week starting on Sun or Mon, so for instance in this year, Week1 can consist of 7 days or 1 day depending upon the value of the reurn type parameter.

In ISO dates a week always begins on a Monday, and ends on a Sunday. The first week of a year is that week which contains the first Thursday of the year, or, equivalently, contains Jan-4.

The following table shows the differences for this year




WEEKNUM(A1,1) WEEKNUM(A1,2) ISO WEEKNUM
Sun 25-Dec-2005 53 52 51
Mon 26-Dec-2005 53 53 52
Tue 27-Dec-2005 53 53 52
Wed 28-Dec-2005 53 53 52
Thu 29-Dec-2005 53 53 52
Fri 30-Dec-2005 53 53 52
Sat 31-Dec-2005 53 53 52
Sun 01-Jan-2006 1 1 52
Mon 02-Jan-2006 1 2 1
Tue 03-Jan-2006 1 2 1
Wed 04-Jan-2006 1 2 1
Thu 05-Jan-2006 1 2 1
Fri 06-Jan-2006 1 2 1
Sat 07-Jan-2006 1 2 1
Sun 08-Jan-2006 2 2 1
Mon 09-Jan-2006 2 3 2
Tue 10-Jan-2006 2 3 2
Sun 08-Oct-2006 41 41 40

[asingh]

Ok..got it..I would require..the calculations as per the ISO method..the Fiscal Week would begin on a Monday..end on the corresponding Sunday.....

thanks

[asingh]

=Start06+7*Week and =Start06+6+7*Week

Formula is working fine..tweaked it a bit...so the First Monday..of Jan 2006..is the beginning....?

thanks a ton.........!

regards,

Asingh