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 almost 7 years ago and it was last updated almost 7 years ago.
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.
Dim sThisFinancialYear As String: sThisFinancialYear = IIf(Month(Date) <= 6, Year(Date), Year(Date) + 1)
Function FinancialYear(dDate As Date) As Integer
If Month(dDate) <= 6 Then
FinancialYear = Year(dDate)
Else
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.