Forum Discussion
Solved
Need Help-Excel formula for date exceptions
I have a formula that mostly works to find the most recent date in a group of cells. I need the formula to ignore the future dates within those cells and just list the most recent date before today. My formula works except on the lines with future dates in them. Instead of returning the date, it returns a False. Since I have multiple exceptions, I had to use the IFS formula:
=IFS(C6="","",MAX(C6:AZ6)>=TODAY(),MAX(C6:AZ6)<TODAY(),MAX(C6:AZ6)<=TODAY(),MAX(C6:AZ6))
The highlighted section is what returns a FALSE statement. I can't figure out how to get it to return the date.
Then see how this works
=IFS(C6="","",MAX(C6:AZ6)>=TODAY(),INDEX(C6:Z6,,MATCH(TODAY(),C6:Z6,1)),MAX(C6:AZ6)<=TODAY(),MAX(C6:AZ6))
That one will return today's date if today's date is in the range. If that's not what you want, you can tweak the MATCH section of the formula
If you have Excel 2021 or newer, this will work, taking advantage of the FILTER function where there are dates in the future to only look at those that are less than today, and get the max of those.
=IFS(C6="","",
MAX(C6:AZ6)>=TODAY(),MAX(FILTER(C6:AZ6,C6:AZ6<TODAY())),
MAX(C6:AZ6)<=TODAY(),MAX(C6:AZ6))mathetes Unfortunately, I have Microsoft Excel Professional Plus 2019, so this won't work. Sorry, I should have added that into the post.
Then see how this works
=IFS(C6="","",MAX(C6:AZ6)>=TODAY(),INDEX(C6:Z6,,MATCH(TODAY(),C6:Z6,1)),MAX(C6:AZ6)<=TODAY(),MAX(C6:AZ6))
That one will return today's date if today's date is in the range. If that's not what you want, you can tweak the MATCH section of the formula
