How to calculate continuous dates
Good morning all, I have an ongoing topic to find gaps in continuous dates, but this is a slightly different problem. For this topic, I am trying to find continuous dates for each ref. Please see example of the data and the columns 'Continuous Start' & 'Continuous End' that I am trying to create. The main difficulty is that some of the start and end dates overlap. If possible, I am also trying to create another column called 'Rank'. The 'Rank' would be an auto generated number that ranks the continuous end dates in decending order i.e the highest rank number would be the MAX continuous end date. There may be about 10+ ranks. Any help eternally appreciated:- (apologies, I have done both a screenshot and table, because the formatting never works on the table) Ref Start Date End Date Continuous Start Continuous End Rank 127 01/01/2018 31/03/2019 01//01/2018 31/03/2020 1 127 01/01/2018 31/03/2019 01//01/2018 31/03/2020 1 127 01/01/2018 31/03/2019 01//01/2018 31/03/2020 1 127 01/01/2018 31/03/2019 01//01/2018 31/03/2020 1 127 01/01/2018 31/03/2019 01//01/2018 31/03/2020 1 127 31/03/2019 31/03/2020 01//01/2018 31/03/2020 1 127 31/03/2019 31/03/2020 01//01/2018 31/03/2020 1 127 31/03/2019 31/03/2020 01//01/2018 31/03/2020 1 127 30/07/2020 01/09/2021 30/07/2020 31/03/2023 2 127 12/08/2020 01/04/2021 30/07/2020 31/03/2023 2 127 31/03/2021 31/03/2022 30/07/2020 31/03/2023 2 127 31/08/2021 30/11/2022 30/07/2020 31/03/2023 2 127 30/11/2021 07/12/2021 30/07/2020 31/03/2023 2 127 31/03/2022 31/03/2023 30/07/2020 31/03/2023 2 442 01/01/2018 29/09/2019 01/01/2018 30/09/2022 1 442 01/01/2018 29/09/2019 01/01/2018 30/09/2022 1 442 01/01/2018 29/09/2019 01/01/2018 30/09/2022 1 442 01/01/2018 29/09/2019 01/01/2018 30/09/2022 1 442 01/01/2018 29/09/2019 01/01/2018 30/09/2022 1 442 01/01/2018 29/09/2019 01/01/2018 30/09/2022 1 442 30/09/2019 30/09/2020 01/01/2018 30/09/2022 1 442 30/09/2019 30/09/2020 01/01/2018 30/09/2022 1 442 30/09/2019 30/09/2020 01/01/2018 30/09/2022 1 442 30/09/2019 30/09/2020 01/01/2018 30/09/2022 1 442 30/09/2019 30/09/2020 01/01/2018 30/09/2022 1 442 30/09/2019 30/09/2020 01/01/2018 30/09/2022 1 442 21/07/2020 30/08/2021 01/01/2018 30/09/2022 1 442 30/09/2020 30/09/2021 01/01/2018 30/09/2022 1 442 30/09/2020 30/09/2021 01/01/2018 30/09/2022 1 442 30/09/2020 30/09/2021 01/01/2018 30/09/2022 1 442 30/09/2020 30/09/2021 01/01/2018 30/09/2022 1 442 02/10/2020 30/09/2021 01/01/2018 30/09/2022 1 442 31/08/2021 31/08/2022 01/01/2018 30/09/2022 1 442 30/09/2021 30/09/2022 01/01/2018 30/09/2022 1 442 30/09/2021 30/09/2022 01/01/2018 30/09/2022 1 442 30/09/2021 30/09/2022 01/01/2018 30/09/2022 1 442 30/09/2021 30/09/2022 01/01/2018 30/09/2022 1 442 30/09/2021 30/09/2022 01/01/2018 30/09/2022 1 442 11/10/2021 13/10/2021 01/01/2018 30/09/2022 1
