Excel Formula amendment

[mrsushi]

I have this formula in Excel (as per below). The result returns the date 07.10.2021

=MAX(IF(H2:H48<TODAY(),H2:H48))-1

The List that its referencing is Column I and has a number of dates

16/09/2021​

23/09/2021​

01/10/2021​

08/10/2021​

15/10/2021​

22/10/2021​

01/11/2021​

10/11/2021​

17/11/2021​

Is there anyway the formula can be tweeked to keep the return value on 7th when today becomes the 15th? As you will see from the above formula, when the day becomes the 15th, it will select 15th - 1 day giving a return value of the 14th.

Any ideas will be much appreciated

Many thanks

 

[Fluff]

That formula will return the 7th when today is the 15th.

 

[mrsushi]

That formula will return the 7th when today is the 15th.

Sorry i meant from tomorrow, how can the formula be amended to select the previous 2 dates in the list ie 7th.
Would it be best to use another formula which selects the previous 2 dates when it pasts the data ie Today itself?

 

[Fluff]

How about

=MAX(IF(H2:H48<TODAY()-2,H2:H48))-1

 

[mrsushi]

How about

=MAX(IF(H2:H48<TODAY()-2,H2:H48))-1

Ta. I give that a try

 

[mrsushi]

Ta. I give that a try

Looks good
thank you

 

[Fluff]

You're welcome & thanks for the feedback.