How do I calculate the date of the first monday of a fiscal year?

**sherlock99** · 07-18-2012, 06:24 AM

Hi,

can anyone help with this one please. Based on just a 4 digit yearly reference value (e.g. 2012), I'm trying to calculate the date of Monday in the week that the 1st April falls. In the examples below, the value in C1 is calculated based on the value in A1 (B1 is just for info and not needed).

A1 B1 C1
Year _ 1st April falls on _ Monday date for that week

2011 Friday Monday 28 March 2011
2012 Sunday Monday 26 March 2012
2013 Monday Monday 01 April 2013
2014 Tuesday Monday 31 March 2014
2015 Wednesday Monday 30 March 2015

I'm using Excel 2007, but if the solution also worked in 2003 it would be a bonus.
Many thanks for any assistance you can provide. I've attached a simple sheet of the above text.

Sherlock99.

**Pete_UK** · 07-18-2012, 06:39 AM

You can use this formula (which will also work in XL2003):

=DATE(E6,4,1)-(WEEKDAY(DATE(E6,4,1),2)>1)*WEEKDAY(DATE(E6,3,31),2)

Format it as you wish to see the result, then copy down.

Hope this helps.

Pete