Forum Discussion

Kaddrik's avatar
Kaddrik
Copper Contributor
Mar 17, 2022

How to find the max value corresponding to a date range

Hello,

 

hope you are all doing good.

I have to work on some Excel report and ...i'm not good in Excel so i hope someone could help me a bit 😛 ...and i guess i will come back several other times 😄

 

ok so... I have a worksheet where are multiple dates and I have to extract a maximum value for each separate date range but make it correspond to the first day of the month. i mean ...

 

16/02/20223
16/02/20221
16/02/20222
19/03/20225
20/04/20222
20/04/20221

 

for 16/02/2022   max would be  3

      19/03/2022   max would be  5

       20/04/2022  max would be 2

 

On another sheet, i would need to get the final max value from the previous sheet and make it correspond to 

 

01/02/2022   which would be 3

01/03/2022   which would be 5

01/04/2022   which would be 2

 

Would someone please be able to help me do this ? 😞

 

Thanks a lot for your time

  • Let's say your data is in Sheet1 (e.g Column A and B).
    And the formula you want to write is "B1" in the sheet below and your formula is calculated based on the 1st day of the month. If so, you can try as below.
                A                                 B
    1   1/2/2022               =MAXIFS(Sheet1!B:B, Sheet1!A:A,">="&A1, Sheet1!A:A,
                                     "<="&eomonth(A1,0)
    2   1/3/2022               drag down formula above
    3   1/4/2022               drag down formula above

    Or, do you want the max of exactly the first date occured in the month?
    For e.g if there are 5/2/2022, 16/2/2022 and 20/2/2022 in Feb 2022, what you want is max of 5/2/2022. Please let me know.

  • Kaddrik 

    Other functions that could be used are MAXIFS or FILTER

    "Month"
    = 1+EOMONTH(UNIQUE(Date),-1)
    
    "Maximum count"
    = MAXIFS(Count, Date, UNIQUE(Date))

    or, using Lambda functions and FILTER,

    "Maximum count"
    = MAP(UNIQUE(Date),
        LAMBDA(d, 
          MAX(FILTER(Count, Date=d))
        )
      )

     

  • Let's say your data is in Sheet1 (e.g Column A and B).
    And the formula you want to write is "B1" in the sheet below and your formula is calculated based on the 1st day of the month. If so, you can try as below.
                A                                 B
    1   1/2/2022               =MAXIFS(Sheet1!B:B, Sheet1!A:A,">="&A1, Sheet1!A:A,
                                     "<="&eomonth(A1,0)
    2   1/3/2022               drag down formula above
    3   1/4/2022               drag down formula above

    Or, do you want the max of exactly the first date occured in the month?
    For e.g if there are 5/2/2022, 16/2/2022 and 20/2/2022 in Feb 2022, what you want is max of 5/2/2022. Please let me know.

    • jegreen's avatar
      jegreen
      Copper Contributor
      Hello, I need to do something very similar but my date range is finding the max every 3 days. Do you know how I could adapt your formula to attempt this?
    • Kaddrik's avatar
      Kaddrik
      Copper Contributor

      hello Starrysky1988 

       

      thanks a lot for your reply.

       

      hmm...not sure it's like this...

      in fact i would have in sheet1 , column A and B,  this

         A                  B

      16/02/20223
      16/02/20221
      16/02/20222
      19/03/20225
      20/04/20222
      20/04/20221

       

      and then in Sheet2 this ...

          A

      01/02/2022     and i have to populate column B with   3

      01/03/2022                                                                    5

      01/04/2022                                                                    2

       

      In the meantime i see my colleague created an intermediate sheet... let's call it Sheet3 ...  where he  shows only 1 occurence of the date

            A                B

      16/02/2022    

      19/03/2022

      20/04/2022

       

      so i created in column B of Sheet3 a formula like MAXIFS(Sheet1!A:A,Sheet1!B:B,$A1)

      Where A1 is date 16/02/2022. and this seems to give me the maximum value when i have several times the same date. I end up with 

          A                B

      16/02/2022    3

      19/03/2022    5

      20/04/2022    2

       

      but then in sheet 3, imagining for 1 month i have several weeks that are reported with their maximum  like this 

       

      09/02/2022  23

      16/02/2022  3

      23/02/2022  10

       

      I still don't understand how i can have in sheet2 , the maximum value corresponding to 1 month

       

      01/02/2022

      01/03/2022

      01/04/2022

       

      which for 01/02/2022  (if i take the previous example) would be 23

       

      is it clearer ?

       

      Thanks a lot for your time

       

       

       

      • Starrysky1988's avatar
        Starrysky1988
        Iron Contributor

        Dear Kaddrik,

        What you want is maximum value for the whole month, then please see my previous reply.

        It is for the whole month.

        For individual date in the month, you can use your formula. Thanks

Resources