Forum Discussion
Solved
Identify if a date is between multiple ranges
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
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"
1 Reply
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"