[SOLVED] Excel VBA Get Financial Year from Date - No Formula

Convert a date to the Financial Year (Fiscal Year) like '01/01/2017' = '2018'. Australian example used. Pure VBA without Excel formulas. Just the solution.

me@jaykilleen.com wrote this over 4 years ago and it was last updated over 4 years ago.

← Back to the Posts

This seemed a lot harder than it should have been as so many examples online ask for VBA and you get an Excel formula. Anyway. Let's do it!

I answer this with both simple VBA and also as a VBA function.

Simple VBA Answer

Dim sThisFinancialYear As String: sThisFinancialYear = IIf(Month(Date) <= 6, Year(Date), Year(Date) + 1)

VBA Function Answer

Function FinancialYear(dDate As Date) As Integer
  If Month(dDate) <= 6 Then
    FinancialYear = Year(dDate)
    FinancialYear = Year(dDate) + 1
  End If
End Function

which can be called like

debug.print FinancialYear(Date) 'which returns "2018" if today's date was 1st January 2018

If you were not using Option Explicit then you can simplify the Simple VBA answer to just sThisFinancialYear = IIf(Month(Date) <= 6, Year(Date), Year(Date) + 1)

Having this as a function is useful if you want to pass in other dates. In some cases I am only ever passing in today's date which is why I call it ThisFinancialYear and other times I am passing in some other date so want to know what FinancialYear that date is within.