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)