Forum Discussion
How to find the max value corresponding to a date range
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.
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.
- 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?
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/2022 3 16/02/2022 1 16/02/2022 2 19/03/2022 5 20/04/2022 2 20/04/2022 1 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
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
Hello Starrysky1988 ,
Sorry for the late reply.
I just tested what you explain and it works ! 🙂
Thanks a lot for your time !!
