Date result

[Mashtonsmith]

Hello,

I need a basic formula that will always give me the next closest 1st May.

Date 1 Date 2
17/10/2014 01/05/2015
01/02/2015 01/05/2015
01/07/2012 01/05/2013

Date 1 = hard dates
Date 2 = shows the result required - but I need the formula to do it please!

Thanks

 

[stumac]

One way (assuming the hard date is in cell A1):

=IF(A1>DATE(YEAR(A1),5,1),DATE(YEAR(A1)+1,5,1),DATE(YEAR(A1),5,1))

I am sure there will be much more efficient ways to achieve this though...

Stu

 

[njimack]

=date(year(a1)+if(month(a1)>5,1,0),5,1)

 

[Tetra201]

Try this:

=DATE(YEAR(A2)+(MONTH(A2)>=5),5,1)

 

[steve the fish]

What should the 1st May 2016 produce? 2016 or 2017?

 

[Mashtonsmith]

Thanks that's spot on !

1st May should produce the NEXT May

so 01/05/2015 should result in 01/05/2016