VBA date functions: test if date intersects date range

A simple and elegant function to test whether a specific date is equal to one of two other dates or lies between them, i.e. whether it intersects the date range given by these two other dates. Returns true if it does and false if it doesn’t.

Table of Contents

Function
Parameters
Usage example

Function

VBA
Function IntersectsDateRange(startDate As Date, endDate As Date, testDate As Date) As Boolean
    IntersectsDateRange = IIf(testDate >= startDate And testDate <= endDate, True, False)
End Function

Parameters

Name	Optional/ Required	Data type	Description
startDate	Required	Date	The beginning of the date range, i.e. the start date.
endDate	Required	Date	The end of the date range, i.e. the end date.
testDate	Required	Date	The date to be tested whether it intersects the date range given by the start date and the end date.

Usage example

VBA
Sub TestIntersectsDateRange()
    Debug.Print IntersectsDateRange(CDate("01-01-2022"), CDate("12-12-2022"), CDate("01-06-2022")) ' true
    Debug.Print IntersectsDateRange(CDate("01-01-2022"), CDate("12-12-2022"), CDate("01-06-2023")) ' false
End Sub