Forum Discussion

christye1979410

Copper Contributor

Mar 30, 2023

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. ...

excel

Formulas and Functions

mathetes
Mar 30, 2023
christye1979410

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

mathetes

Silver Contributor

Mar 30, 2023

christye1979410

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))

christye1979410
Copper Contributor
Mar 30, 2023
mathetes Unfortunately, I have Microsoft Excel Professional Plus 2019, so this won't work. Sorry, I should have added that into the post.
- mathetes
  Silver Contributor
  Mar 30, 2023
  christye1979410
  
  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
  - christye1979410
    Copper Contributor
    Mar 30, 2023
    That worked! TY so much! I've been working on this specific formula for 2 days. I really appreciate your help.