Calculating a Fiscal Week

[Eaglboy1]

Hey guys,

I'm trying to come up with a formula that will calculate the fiscal week based on today's date, but I'm having a hard time with it because my company uses a strange fiscal start date. Our fiscal year starts on the Sunday before the first Wednesday that falls in February. For example, fiscal week 1 for 2016 started on 1/31/2016, 2017 starts on 1/29/17, and 2018 starts on 1/28/2018. Any ideas on how I could make this work or is it a lost cause?

Thanks in advance for your help!

 

[Eaglboy1]

Hey, as long as you're willing to post the formula that I can copy and paste into my Excel spreadsheet, I don't care how long winded it is.

 

[Tetra201]

Try this:

=1+INT((TODAY()-IF(DATE(YEAR(TODAY()),2,1)-WEEKDAY(DATE(YEAR(TODAY()),2,1),1)+1<=TODAY(),DATE(YEAR(TODAY()),2,1)-WEEKDAY(DATE(YEAR(TODAY()),2,1),1)+1,DATE(YEAR(TODAY())-1,2,1)-WEEKDAY(DATE(YEAR(TODAY())-1,2,1),1)+1))/7)

or place =TODAY() in cell A1 and try a shorter version:

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

 

[Eaglboy1]

That works perfectly. Thank you kindly!

 

[Tetra201]

You are welcome.