Identify if a date is between multiple ranges

[StevenMorgan81]

Hi folks,

Looking for a bit of help in adding a Custom Column to define if a date is between two sets of date ranges i.e. If [Date] Between [Valid Start 1] and [Valid End 1] then Valid else If [Date] [Valid Start 2] and [Valid End 2]  then "Valid" else "Invalid".

[Valid Start/End 2] may contain null values where it is not present in the dataset.

Sample Table:

Date	Valid Start 1	Valid End 1	Valid Start 2	Valid End 2	Valid?
01/06/2022	01/01/2021	null	null	null	Valid
01/06/2022	01/01/2021	01/12/2022	null	null	Valid
01/06/2022	01/01/2021	01/01/2022	null	null	Invalid
01/06/2022	01/01/2021	31/12/2021	01/04/2022	null	Valid
01/06/2022	01/01/2021	01/01/2022	01/04/2022	01/07/2022	Valid
01/06/2022	01/01/2021	01/01/2022	01/04/2022	01/05/2022	Invalid

The Valid Start / End dates are actually stored in a different table and have been appended by merging tables - if its easier to "lookup" without the merge no problem going down that route.

Any help very welcome.

Steve

[Greg_Deckler · Accepted solution]

@StevenMorgan81 Add a custom column like: 

= if ( [Date] >= [Valid Start 1] and ( [Valid End 1] = null or [Date] <= [Valid End 1]) and not([Valid Start 1] = null and [Valid End 1] = null ) ) or ( [Date] >= [Valid Start 2] and ( [Valid End 2] = null or [Date] <= [Valid End 2]) and not([Valid Start 2] = null and [Valid End 2] = null ) ) then "Valid" else "Invalid"