Trying to return the week number of dates in a fiscal year

Hi, I have been working on a spreadsheet that uses week numbers. It was originally written for a standard calendar year, but the company is moving to a fiscal calendar that starts on the first Sunday of May each year.

I have tried to convert many of the other suggested formulas but I always run into issues where on some of the future years some of the first few days in May return a week number of 0? I am usually fairly good at creating formulas but I can't seem to wrap my head around this one.

To add to the problem all of the end user PC's have just been rolled back to Office 2003 due to some compatibility issues with other software we use, and I will not be able to get IT to add any new tool packs to all of the user PC's.

One of the formulas I have tried is;

Please Login or Register  to view this content.

Any help would be appreciated.

Thank you,

Thanks vlady that formula has fixed some of the transitional weeks from showing up as week number 0 but it is still one week off on the first few days of May when there are less than 4 days.

Like in 2014 week # 1 should start on Sunday May 4th. I am not sure I will be able to do this without using a vlookup to assign the correct fiscal year starting point?

Thanks again for the help!

Date of First Sunday in May:
fiscal_start =DATE(some_year, 5, 1) + (7 - WEEKDAY(DATE(some_year, 5, 1), 11))

Once you've got that....
INT( (test_date - fiscal_start) /7 )+1

something like that should do it, right?

Thanks, Ben I will give that a shot when I get back to the office in a few hours.

Originally Posted by daddylonglegs

...I adapted a formula I use for ISO week numbers

=INT((M10-WEEKDAY(M10)-DATE(YEAR(M10+246-WEEKDAY(M10))-1,5,7))/7)+2

Thank you, this worked! I might make my deadline after all!

Thank you, Thank you, Thank you!