The dates in column D and F are in the workbook.
Based on the Month (and year) in F13 I need to construct a formula to find:
1/ the last date in May in column D, which is 27/5 (D12)
and
2/ the last date the month before May, which is shown in column D, and which is 29/4 (D8).
How do we construct these two formulas ?
Please see the enclosed workbook.
Hello
Try the following:
Max for May:
Formula:
=MAX(INDEX((MONTH(D2:D19)=MONTH(F16))*(D2:D19),0))
And previous month:
Formula:=MAX(INDEX((MONTH(D2:D19)=MONTH(F16)-1)*(D2:D19),0))
DBY
If your list contains dates across years, try these adapted versions of DBY's formula
=MAX(INDEX((TEXT(D2:D19,"mmyyyy")=TEXT(F16,"mmyyyy"))*(D2:D19),0))
and
=MAX(INDEX((TEXT(D2:D19,"mmyyyy")=TEXT(EDATE(F16,-1),"mmyyyy"))*(D2:D19),0))
Life's a spreadsheet, Excel!
Say thanks, Click *
Thank you, DBY and Ace_XL
None of the suggestions calculate the YEAR, so if I have both 2014 and 2015 in the same range the result refers to 2015. At least on my PC.
But I solve this by limit the range to one year only in the formulas, and the formulas works fine.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks