I have a table of dates (not sorted). For each row, I need to find:

1. the RANK the Report Date is
2. the Report Date's Report Month
3. the Report Date's Report Quarter
4. the the immediate previous Report Date
5. the greatest Report Date in the immediate previous Report Month
6. the greatest Report Date in the immediate previous Report Quarter

I have 1-4 figured out. I'm not sure how to get 5 and 6.

Here is my data, and the various formulas I have:

Formulas:

• B1:

  ={
      "Report Date Rank";
      ARRAYFORMULA(
          IF(
              A2:A <> "",
              RANK(
                  A2:A,
                  A:A
              ),
          )
      )
  }
  

• C1:

  ={
      "Report Month";
      ARRAYFORMULA(
          IF(
              A2:A <> "",
              TEXT(A2:A, "YYYY-MM"),
          )
      )
  }
  

• D1:

  ={
      "Report Quarter";
      ARRAYFORMULA(
          IF(
              A2:A <> "",
              YEAR(A2:A) & " Q" & ROUNDUP(MONTH(A2:A)/3, 0),
          )
      )
  }
  

• E1:

  ={
      "Previous Report Date";
      ARRAYFORMULA(
          IF(
              A2:A <> "",
              XLOOKUP(
                  B2:B   1,
                  B:B,
                  A:A,
              ),
          )
      )
  }
  

For 5 and 6, I was thinking I could RANK the Report Month and Report Quarter columns, but I don't know how to use that information to get the greatest date in the previous "rank".

So, for example, for 5, for the row with 2022-08-19 for Report Date, 5 and 6 would be:

• Previous Month Report Date = 2022-07-26 because that is the greatest Report Date in the 2022-08-19's previous month
  • 2022-08-19's Report Month is 2022-08
  • The previous Report Month is 2022-07
  • The largest Report Date in 2022-07 is 2022-07-26
• Previous Quarter Report Date = 2022-06-27 because that is the greatest Report Date in the 2022-08-19's previous quarter
  • 2022-08-19's Report Quarter is 2022 Q3
  • The previous Report Quarter is 2022 Q2
  • The largest Report Date in 2022 Q2 is 2022-06-27

CodePudding user response：

Try approximate match (next smallest) on the sorted dates:

=ArrayFormula(if(A2:A="",,xlookup(eomonth(A2:A,-1),sort(A2:A),sort(A2:A),,-1)))

and

=ArrayFormula(if(A2:A="",,xlookup(eomonth(A2:A,-(mod(month(A2:A)-1,3) 1)),sort(A2:A),sort(A2:A),,-1)))