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!

 

[markmzz]

Try this too:

=INT((A2-("7/1/"&YEAR(A2)-(MONTH(A2)<7))+WEEKDAY("7/1/"&YEAR(A2)-(MONTH(A2)<7))-1)/7)+1

Markmzz

 

[Christopher lee]

Hi Mcheckai,
i would like to ask if i hv a lot data in many many month and week......then if i only want to calculate the median of the last 4week data izzit possible or not??pls hlp me pls......