Calculate week number for Fiscal Year Dates

**katie_10042** · 04-26-2010, 10:38 AM

My fiscal year starts on October 1st and I have a total of 53 weeks in a year. Saturday is considered the first day of the week. How do I calculate the week number of a specific date? For instance, if the date is 4/10/2010, the week number should be 29.

**ChemistB** · 04-26-2010, 10:58 AM

Try this formula
=53-WEEKNUM(DATE(YEAR(B1)-1,10,1))+WEEKNUM(B1)
where your current date is in B1. It does however give me 28 with your example. Double check your count. If it really is 29, then modify the formula with +1. Does that work for you?

**Palmetto** · 04-26-2010, 10:59 AM

EDIT: deleted formula

**ChemistB** · 04-26-2010, 11:10 AM

Oh, My formula will only work up until October 1st. Oppps, it expands to
=IF(MONTH(B1)>=10,WEEKNUM(B1)-WEEKNUM(DATE(YEAR(B1),10,1))+1,53-WEEKNUM(DATE(YEAR(B1)-1,10,1))+WEEKNUM(B1))

I'm thinking someone here can simplify.

**sailepaty** · 04-26-2010, 12:07 PM

Try this,

A1=04/10/2010

=INT((A1-WEEKDAY(A1+1)-DATE(YEAR(A1+7-WEEKDAY(A1+1))-(MONTH(A1)<10),10,1))/7)+2

Reagards

**katie_10042** · 04-26-2010, 05:00 PM

Thanks sailepaty! The formula works perfectly!

**daddylonglegs** · 04-26-2010, 06:06 PM

If A1 is 31st December 2011 sailepaty's formula returns -38, and you get similar results for the last few days of some other years.

Assuming your week numbers are defined as follows:

week 1 starts on 1st October then week 2 starts on the next Saturday after that, then you can use this formula to get the week numbers

=INT((A1-DATE(YEAR(A1+92)-1,10,1)-WEEKDAY(A1+1))/7)+2

Like WEEKNUM function that formula can give you 54 in certain circumstances (only on 30th Sept if that day is a Saturday and the year is a leap year - although the next one isn't until 2028!)

**sailepaty** · 04-26-2010, 06:48 PM

Originally Posted by daddylonglegs

If A1 is 31st December 2011 sailepaty's formula returns -38, and you get similar results for the last few days of some other years.

I think this one works better.

=INT((A1-WEEKDAY(A1+1)-DATE(YEAR(A1)-(MONTH(A1)<10),10,1))/7)+2

Reagrds

**FDibbins** · 12-14-2016, 11:51 PM

ILP welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.