Excel Formula for moving fiscal year start date

zinzah

New Member
Joined
Mar 9, 2010
Messages
49
Good morning all.....
Done a lot of looking and have made some progress but stuck now....

My fiscal year does not always start on the same date....This is for some accounting reason...but it's been this way forever as far as i know...
What i do know is that the fiscal year starts on the SUNDAY before the LAST WEDNESDAY in MARCH...
=DATE(YEAR(B3),MONTH(B3)+1,0)+MOD(-WEEKDAY(DATE(YEAR(B3),MONTH(B3)+1,0),2)-2,-7)-5
This returns the Sunday before the Last Wednesday of each month....
I managed to get this far (with a lot of research) but cant get further....
I have a list of dates for 10 years in column B....would love to add to Column C what the FY starting date for that date is.....
In 2017...The last Wednesday in March was the 29th...the prior Sunday is the 26th...That is my FY start date
In 2018...The last Wednesday in March was the 28th...the prior Sunday is the 25th...That is my FY start date

I would love to be able to have any given date in Col. B with that date's FY Start date in order to calulate fiscal days, weeks and quarters...

Any helps would be appreciated....
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Does this work?


Book1
BC
1DateFY Start Date
224/03/201826/03/2017
325/03/201825/03/2018
425/03/201727/03/2016
526/03/201726/03/2017
Sheet1
Cell Formulas
RangeFormula
C2=IF($B2YEAR($B2),3,31)-MOD(DATE(YEAR($B2),3,31)+3,7)-3,DATE(YEAR($B2)-1,3,31)-MOD(DATE(YEAR($B2)-1,3,31)+3,7)-3,DATE(YEAR($B2),3,31)-MOD(DATE(YEAR($B2),3,31)+3,7)-3)


WBD
 
Upvote 0
WBD....

WOW!!!!!!! Awesome!!! Looks like it works perfectly!!! can't believe you did that in 20 minutes!!!

THANK YOU!!!
 
Upvote 0
Most of it is the same formula three times. Sunday before last Wednesday in March for {YEAR} is:

Code:
DATE({YEAR},3,31)-MOD(DATE({YEAR},3,31)+3,7)-3

WBD
 
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,305
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top