Is a date within range of dates

Financial year 2017 - example

Does a particular date fall within the financial year 2017? In other words, is the date in the range 1 July 2016 to 30 June 2017?

Code 1: Function FY_17 determines whether date falls in Australian financial year 2017, and returns Boolean TRUE

Public Function FY_17(Dte As Date) As Boolean
' In Australia, the financial (fiscal) year runs from 1 July to 30 June
' - the financial year is normally denoted by the year in which it ends
' - for example, the period 1 July 2016 to 30 June 2017 is denoted by FY2017 or FY17

    If Dte >= DateValue("1 July 2016") And Dte <= DateValue("30 June 2017") Then
        FY_17 = True
    Else
        FY_17 = False
    End If

End Function

About code 1 :

Line 6: logic [1 Jul 16] <= Dte <= [30 Jun 17]. The code contains two logical tests Dte >= DateValue("1 July 2016") and Dte <= DateValue("30 June 2017"), and returns TRUE if both tests are TRUE

Use unambiguous dates in the style dd mmm yyyy, such as DateValue("1 July 2016"). Avoid literals like #07/01/16# that could be interpreted by the reader as either 7th January, or July 1st.

Date to FY

Code 2: Function FY converts date to Australian financial year format, range 1 July 2017 to 30 June 2022

Public Function FY(Dte As Date) As Variant

Select Case Dte
    Case DateValue("1 July 2016") To DateValue("30 June 2017")
        FY = 2017
    Case DateValue("1 July 2017") To DateValue("30 June 2018")
        FY = 2018
    Case DateValue("1 July 2018") To DateValue("30 June 2019")
        FY = 2019
    Case DateValue("1 July 2019") To DateValue("30 June 2020")
        FY = 2020
    Case DateValue("1 July 2020") To DateValue("30 June 2021")
        FY = 2020
    Case DateValue("1 July 2021") To DateValue("30 June 2022")
        FY = 2022
    Case Else
        FY = VBA.CVErr(xlErrValue)
End Select

End Function

This example was developed in Excel 2016 Pro 64 bit.
Revised: Friday 24th of February 2023 - 10:37 PM, Pacific Time (PT)