Good morning friends

I have the following set of formulas with which I intend to search for the pair of dates with the greatest number of days existing within a set of consecutive dates through which I can determine records of compliance or non-compliance with certain goals based on the dates of certain events

=INDEX(SORT(FILTER(MATRIX!$U$2:$U$3018; MATRIX!$AU$2:$AU$3018="");;1);1)

=INDEX(SORT(FILTER(MATRIX!$U$2:$U$3018; MATRIX!$AU$2:$AU$3018="");;1);2)

=INDEX(SORT(FILTER(MATRIX!$U$2:$U$3018; MATRIX!$AU$2:$AU$3018<>"");;1);1)

=INDEX(SORT(FILTER(MATRIX!$U$2:$U$3018; MATRIX!$AU$2:$AU$3018<>"");;1);2)

1. As can be seen in Column “U” of Sheet 3 (MATRIX), there are a total of 18 individual dates distributed from Cell “U3” to Cell “U20”

2. Formulas should sort and filter dates between consecutive events that correspond when AU=””, 14 date pairs (U3:U5, U5:U7, U7:U8, U8:U9, U9:U10, U10:U11, U11:U12, U12:U13, U13:U14, U14:U16, U16:U17 U17:U18, U18:U19 and U19:U20) and when AU<>”” 02 pairs of dates (U4:U6 and U6:U15) according to Image 1.

3. The formulas should individually evaluate the result of the difference between the most recent date and the oldest date corresponding to each pair of dates obtained in the previous point, selecting the pair of dates with the greatest number of days when AU=”” and when AU<>””, as shown in Image 1

4. Consequently, the pair of dates with the highest value when AU=”” is from 07/07/2023 to 01/12/2023 = 147 days, while the pair of dates with the highest value when A< >”” is from 04/30/2023 to 01/17/2024 = 262 days.

5. However, as can be seen in Sheet 65 (BILLBOARD) when applying the formulas, although the data varies the position and/or other pairs of dates with a greater number of days are introduced, these formulas only take into account the first pair of dates from bottom to top when AU=”” from 01/01/2023 to 01/29/2023 = 28 days and when AU<>”” from 04/30/2023 to 01/17/2024 = 262 days.

I will be very grateful for your help

I have the following set of formulas with which I intend to search for the pair of dates with the greatest number of days existing within a set of consecutive dates through which I can determine records of compliance or non-compliance with certain goals based on the dates of certain events

=INDEX(SORT(FILTER(MATRIX!$U$2:$U$3018; MATRIX!$AU$2:$AU$3018="");;1);1)

=INDEX(SORT(FILTER(MATRIX!$U$2:$U$3018; MATRIX!$AU$2:$AU$3018="");;1);2)

=INDEX(SORT(FILTER(MATRIX!$U$2:$U$3018; MATRIX!$AU$2:$AU$3018<>"");;1);1)

=INDEX(SORT(FILTER(MATRIX!$U$2:$U$3018; MATRIX!$AU$2:$AU$3018<>"");;1);2)

1. As can be seen in Column “U” of Sheet 3 (MATRIX), there are a total of 18 individual dates distributed from Cell “U3” to Cell “U20”

2. Formulas should sort and filter dates between consecutive events that correspond when AU=””, 14 date pairs (U3:U5, U5:U7, U7:U8, U8:U9, U9:U10, U10:U11, U11:U12, U12:U13, U13:U14, U14:U16, U16:U17 U17:U18, U18:U19 and U19:U20) and when AU<>”” 02 pairs of dates (U4:U6 and U6:U15) according to Image 1.

3. The formulas should individually evaluate the result of the difference between the most recent date and the oldest date corresponding to each pair of dates obtained in the previous point, selecting the pair of dates with the greatest number of days when AU=”” and when AU<>””, as shown in Image 1

4. Consequently, the pair of dates with the highest value when AU=”” is from 07/07/2023 to 01/12/2023 = 147 days, while the pair of dates with the highest value when A< >”” is from 04/30/2023 to 01/17/2024 = 262 days.

5. However, as can be seen in Sheet 65 (BILLBOARD) when applying the formulas, although the data varies the position and/or other pairs of dates with a greater number of days are introduced, these formulas only take into account the first pair of dates from bottom to top when AU=”” from 01/01/2023 to 01/29/2023 = 28 days and when AU<>”” from 04/30/2023 to 01/17/2024 = 262 days.

I will be very grateful for your help

#### Attachments

Last edited: