Convert date to its Financial Year End Date
To convert a date to its financial year end date you can use the following formula, assuming the date is in cell A1.
=DATE(YEAR(A1)+(MONTH(A1)>6),6,30)
The (MONTH(A1)>6) part returns TRUE when the month number of the date is above 6. In Excel TRUE = 1, hence 1 is added to the year of the date.
If the month number is 6 or less it returns FALSE and in Excel FALSE = 0, so the year is left unchanged.
A shorter formula that provides the same result is.
=DATE(YEAR(A1+184),6,30)