Return Year End Date it falls in

[tlc53]

Hi there,

I would like to calculate a year end date, based on a changeable date.

For example, say the year end date is 30 June.

eg. From start date 01/12/2017
I would like it to return the year end date it falls in, so 30/06/2018.

eg. From start date 01/05/2017
Returns 30/06/2017

Cell F13 has the start date
Cell E7 has the current financial year end, so I would like it to get the dd/mm from here.

I've looked around for a solution but I've had no luck.

Has anyone got any suggestions please? Thank you

 

[Phuoc]

Try this in E7:

=DATE(YEAR(F13+184),6,30)

 

[tlc53]

Thanks but the 30 June may change, to say 31 March.
Both the year end date and the start date can change.
Cell E7 is the Year End Date

 

[Phuoc]

Maybe?

=DATE(YEAR(F13)+(TEXT(E7,"mmdd")<TEXT(F13,"mmdd")),MONTH(E7),DAY(E7))

 

[tlc53]

Awesome! That works! Thank you