Convert Fiscal date to corresponding week number

[dagindi]

Good afternoon everyone,

I have a spreadsheet that I use to convert a purchase order ship date from the actual date to the corresponding week it falls out on.

The fiscal year always starts on February 1 regardless of the day of the week.

The problem i am encountering is when the year changes. As soon as I enter 01/01/2010, the response I get is -4, where as 12/31/2009 is 48.

I am using the following formula that I found somewhere, where R2 = 02/01/2009 (02/01/2009 falls out on a Sunday).

=INT((R2-DATE(YEAR(R2),2,1)-WEEKDAY(R2,1))/7)+2

My guess is that I need to make the formula "not care about" the day of the week.

Thanks in advance for your help.

[ncmay]

Try the formulas below:

Please Login or Register  to view this content.

or enter the beginning fiscal date in another cell, example (Q2).

Please Login or Register  to view this content.

[dagindi]

I went with the first code suggestion and it works great. Thank you!!

I would like to add (for other noobs like me ) that when the fiscal year changes you need to change 2009 to 2010 in the code.

[daddylonglegs]

If you replace the "hard-coded" year in the formula with YEAR(R2-31) then you won't ever need to change the formula......i.e.

=INT((R2-DATE(YEAR(R2-31),2,1)-WEEKDAY(R2,1))/7)+2