Forum Discussion
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 |
2 Replies
ClemFandango Please Share your expected output.
Hi Mahesh0016
The calculated columns 'Continuous Start', 'Continuous End' and 'EndRank' are showing the expected/desired output at this stage. By creating a continuous start date & continuous end date I will be able to identify the date when a 'Ref' is 'New', 'Lost' or 'Returned' and this will be calculated within a date table
