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),6,30) 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.

Related Posts