I believe I need a multi-row formula for this but not sure how to begin. I need to verify if the range of dates between 2 columns and multiple rows show no gaps.
For example, if the "chk_out_dt" is one day after the "chk_in_dt" from the previous row (if blank/null, the last "chk_in_dt" that is not blank), then valid:
unit_no | chk_out_dt | chk_in_dt | Valid Date Range |
18000001 | 6/10/2020 | 9/22/2020 | Yes |
18000001 | 9/23/2020 | 6/28/2023 | Yes |
18000001 | 6/29/2023 | 4/16/2024 | Yes |
18000001 | 4/17/2024 | Yes | |
18002964 | 3/30/2021 | 3/14/2023 | Yes |
18002964 | 3/15/2023 | 5/5/2023 | Yes |
18002964 | 5/6/2023 | 4/18/2024 | Yes |
18002964 | 4/19/2024 | Yes | |
18002964 | 4/20/2024 | No |
That last row is Not Valid since the "chk_out_dt" (4/20/2024) is 2 days after the last "chk_in_dt" (4/18/2024)
解決済! 解決策の投稿を見る。