Scan row of dates to return most recent date before today

[studio95]

Morning all,

I have the following calculation in my sheet :-

=IF(AT154="","",MAX(AT154:CI154))

if there is a date in present in AT154 then this formula will look along that row and in cell AJ154 will return the value of the most recent date in that row. However we have now started to record future dates in this row which I want to ignore and only return a value in AJ154 of the most recent date prior to today.

Could someone please modify this to achieve that?

Thank you kindly.

 

[wideboydixon]

=IF(AT154="","",MAX(IF(AT154:CI154<TODAY(),AT154:CI154)))
<today(),at154:ci154)))

Entered with Ctrl+Shift+Enter. If you have MAXIFS available you could also do it like this:

=IF(AT154="","",MAXIFS(AT154:CI154,AT154:CI154,"<"&TODAY()))

WBD</today(),at154:ci154)))

 

[studio95]

=IF(AT154="","",MAX(IF(AT154:CI154<TODAY(),AT154:CI154)))
<today(),at154:ci154)))

Entered with Ctrl+Shift+Enter. If you have MAXIFS available you could also do it like this:

Worked perfectly thank you so much </today(),at154:ci154)))