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!

 

[Gerald Higgins]

Er . . . for 2018, IF 28th Jan is a Sunday (I haven't checked), then 31st Jan must be a Wednesday, then 7th Feb must be a Wednesday, and 4th Feb must be a Sunday.

So why doesn't fiscal 2018 start on 4th Feb ?

 

[Eaglboy1]

You know what, now that you say that, you're correct. I misread the way my company does fiscal years. Lol. Those fiscal year dates I gave are correct though. So I guess the fiscal year starts the Sunday of the week that Feb 1st falls in. My apologies!

 

[Gerald Higgins]

OK . . . what if Feb 1st is a Sunday ?

We just need to define the rule really clearly.

 

[Eaglboy1]

2/1/2015 was the first day of the fiscal year and a Sunday.

 

[Gerald Higgins]

OK . . . so can we express the rule like this . . .

Fiscal year starts on Feb 1st if that is a Sunday, and if it's not a Sunday, then fiscal year starts on the last Sunday in January ?

 

[Eaglboy1]

Yes sir. I would say that's about as accurate as we can get on it. Thanks for working with me on this.

 

[Gerald Higgins]

OK cool, so now we know the rule for determining the date of the start of your fiscal year.

Can I just check, what exactly is the problem you need to solve ?

Is it about being given some random date, and having to work out what was the start of the fiscal year for that random date ?

 

[Eaglboy1]

Honestly, I'm just trying to set up a formula that will allow me to use today's date to figure out what the fiscal week is. Meaning today is week 51 of the fiscal year and I have a hard time remembering that. I'm almost ALWAYS working in Excel, so it would be helpful to just have a running formula that tells me what week it is any time I need it.

So I'm hoping to find one that will use today's date and spit out what the current fiscal week is.

 

[Gerald Higgins]

OK I need to think about this a bit.

I'm sure this is do-able, but I can't think of a quick and easy way.

I can think of a long-winded way . . .