I have data which contains quote dates and sold dates with flags showing which row is for what. I want to calculate the earliest date a transaction happened, the latest date and the number of days between the quote taking place and the transaction taking place. Quote column being 1 means it is a quote, sold column being 1 means it is a transaction that sold. Min date and max date are the dates related to the sold transaction. days (max) would just be the difference between the quote date and the max date which is found. Any idea?
Quote No | date | Sold | Quote | Max date | Min Date | Days (Max) | Days (Min) |
1 | 1/1/2021 | 1 | |||||
1 | 2/1/2021 | 1 | |||||
1 | 12/3/2020 | 1 | 2/1/2021 | 1/1/2021 | 60 | 29 | |
2 | 5/1/2021 | 1 | |||||
2 | 8/1/2021 | 1 | |||||
2 | 3/1/2021 | 1 | 8/1/2021 | 5/1/2021 | 153 | 61 |
Solved! Go to Solution.
Check out the attached. I may have over simplified this, so please review that it hits the mark.