Formula to calculate fiscal year week based on date

[mcheckai]

Hello,

I have a two column spreadsheet, the first column contains a date and the second column should calculate the fiscal week for the date in the first column.

The fiscal year begins on 7/1 of each year and each week begins on Sunday.

The below formula is close but it calculates the fiscal week as 52 for 7/1/2000...it should be week 1.

=INT((A2-DATE(YEAR(A2+183),-5,-1))/7)

Your assistance is appreciated!

 

[RonB1111]

Try
=WEEKNUM(A2,1)-26+IF(MONTH(A2)<7,52,0)

 

[mcheckai]

Thank you for the reply! The formula works for the most part but I get a strange result for the last few dates I tried below. Any ideas?

12/30/2000 - (week) 27
12/31/2000 - (week) 28
1/1/2001 - (week) 27

 

[RonB1111]

Here a different approach that deals with that issue.
Enter this formula in B2 and copy down. If you use a column other than B, change the 2 instances of B1 in the formula to whatever column you're entering this formula.

=IF(AND(MONTH(A2)=7,DAY(A2)=1),1,IF(WEEKDAY(A2,1)=1,B1+1,B1))

 

[mcheckai]

Thanks again for your continued help with this. The latest formula gave me the below results. It seems the first formula you provided was closer.

7/1/2000 - (week) 1
8/1/2000 - (week) 1
9/1/2000 - (week) 1

 

[RonB1111]

Thanks again for your continued help with this. The latest formula gave me the below results. It seems the first formula you provided was closer.

7/1/2000 - (week) 1
8/1/2000 - (week) 1
9/1/2000 - (week) 1

The 2nd formula worked properly for me. In cell A2 was 7/1/2000 and subsequent dates were in cells below that. The formula was placed in cell B2 and copied down. I haven't been able to duplicate your results. Could you explain further where your dates are stored as well as the formula.

 

[mcheckai]

It sounds like we're doing the same thing but getting different results. Here's the my setup in Excel A2 = 7/1/2000, A3 = 8/1/2000, A4 = 9/1/2000, A5 = 12/30/2000, A6 = 12/31/2000, A7 = 1/1/2001.

I pasted the formula, =IF(AND(MONTH(A2)=7,DAY(A2)=1),1,IF(WEEKDAY(A2,1)=1,B1+1,B1)), into B2 and copied down through B7.

Here are the results I get in column B, B2 = 1, B3 = 1, B4 = 1, B5 = 1, B6 = 2, B7 = 2

 

[RonB1111]

OK, my setup was by day rather than month. IE in A2: 7/1/2000, in A3 7/2/2000 etc.
My 2nd formula will only work for a list of days in order beginning with 7/1.
The 1st formula will be close but with the anomalies you noted.
If I think of a solution for monthly date listing, I'll post back.

 

[mcheckai]

Thank you! This will work perfectly for my situation. I was testing with random dates but I now understand how the formula is working. Thanks again!!

 

[RonB1111]

Glad it's now working for you. Here's an alternative formula that works on both daily and monthly dates beginning with any July 1:

=IF(AND(MONTH(A2)=7,DAY(A2)=1),1,ROUNDUP((A2-DATE(YEAR(A2)-IF(MONTH(A2)<7,1,0),7,1)+WEEKDAY(DATE(YEAR(A2)-IF(MONTH(A2)<7,1,0),7,1)))/7,0))