Forum Discussion

Kaddrik's avatar
Kaddrik
Copper Contributor
Mar 17, 2022
Solved

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 ...
Formulas and Functions
  • Starrysky1988's avatar
    Starrysky1988
    Mar 18, 2022

    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.

Starrysky1988's avatar
Starrysky1988
Iron Contributor
Mar 18, 2022

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
Oct 19, 2023
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?

Resources