Display date from a range within last 6 months

[jwatson15]

Hi all,

Been trying various different formulas but I'm unable to work a formula for what I need.

What I would like to do is from a range of given dates, display the oldest date within the last 6 months.
So for example..

C4 - 21/03/2023
C5 - 15/04/2023
C6 - 02/09/2023
C7 - 12/10/2023
C8 - 02/01/2024

So from the above range of dates, in Cell D11, display the oldest date that is within the last 6 months of TODAYs date.
So the answer would be 02/09/2023 and I would like that to populate in D11.

Hopefully that makes sense, appreciate any help on this.

 

[DanteAmor]

How about:

varios 10ene2024.xlsm
	C
4	21/03/2023
5	15/04/2023
6	02/09/2023
7	12/10/2023
8	02/01/2024
9
10
11	02/09/2023
Hoja3

Cell Formulas
Range	Formula
C11	C11	=SMALL(IF(C4:C8>=TODAY()-180,C4:C8),1)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Joe4]

DanteAmor,

There are certain dates in which that formula may not work properly.
Minor modification that should work a little better:
Replace:

TODAY()-180

with

EDATE(TODAY(),-6)

Otherwise, if the current date was 31/12/2024, it wouldn't see the following dates as being within the last 6 months:
01/07/2024
02/07/2024
03/07/2024

(note: dates represented in dd/mm/yyyy format)

 

[jwatson15]

This is absolutely perfect, thank you both so much! It works perfectly and tweaked with the suggestion from Joe

 

[Joe4]

You are welcome.
Glad we were able to help!

 

[Peter_SSs]

@jwatson15
Please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers.