Number of days between pairs of dates

[JUES]

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

 

[p45cal]

Have a look at the formulae in cells BC10 and BG10 on the BILLBOARD sheet:

=LET(w,SORT(FILTER(MATRIZ[DATE],MATRIZ[Code]=""),,-1),x,DROP(w,-1),y,DROP(w,1),d,x-y,TRANSPOSE(INDEX(HSTACK(d,y,x),XMATCH(MAX(d),d))))

and

=LET(w,SORT(FILTER(MATRIZ[DATE],MATRIZ[Code]<>""),,-1),x,DROP(w,-1),y,DROP(w,1),d,x-y,TRANSPOSE(INDEX(HSTACK(d,y,x),XMATCH(MAX(d),d))))

1. Is it correct?
2. Is it any help?

ps. Have also made these two formulae into named lambda formulae called Empty and NotEmpty and used them in cells BC15 and BG15.

 

[JUES]

Good afternoon, i hope you are well. Thank you for your diligent and timely response to my concern in form and substance. With what you sent me, I obtained the expected results. However, I have been able to observe the following:

1. Sheet3 (MATRIX) has the particularity of receiving data from two different forms, which is why the amount of information contained in said sheet varies from month to month due to the items that are added periodically, so when inserting more data (Date ) and Code) in Cell “U3” immediately on Sheet65 (BILLBOARD) in the cells where you placed the formulas the error #NAME?

2. When trying to copy and paste the formulas in the corresponding places, the error #NAME? appears. on Sheet 65 (BILLBOARD) which is a summary of all the results obtained, so due to the distribution of the sheet, the information regarding DAYS must appear in Cells AF13 and AO13 while SINCE must appear in Cells AF18 and AO18 and the UNTIL must appear in Cells AF20 and AO20

3. Assuming that the correction would not be such a complex formula and that I could manipulate it easily, I planned to combine its components in these formulas:

=IF(DAYS(FY20;FY18)>DAYS(TODAY():FY20);TODAY(); FY20
=IF(DAYS(YEAR20;FY18)>DAYS(TODAY():YEAR20);TODAY(); YEAR20

In order that if no more events were temporarily or permanently recorded and the difference between TO – FROM was less than that of TODAY() – TO, the date to be displayed in Cells AF20 and AO20 should be TODAY() and with this allow that the record will be updated automatically, so if there was a way to integrate into its formulation what I was contemplating doing, it would fully comply with what I had planned.