Filter within the last 365

[garypea123]

Good Morning All,

Hopefully a simple one but I am looking to create an auto-filter which will only return results for anything in the last 365 days (A rolling list so to speak)

Below is a very simplified table

In F2 I have put =FILTER(B2:D4,B2:B4<365,) which is always going to be wrong as it would not understand the 365 as being days. But hoping somebody can tell me the correct way to write this.

 

[etaf]

probably simpler formulas
But how about
=FILTER(B2:D10,(B2:B10<DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))*(B2:B10<>""))

rather than 365 - i have used 1 year here
DATE(YEAR(TODAY())-1

BUT you could change to
=FILTER(B2:D10,(B2:B10<DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())-365))*(B2:B10<>""))

Book2
	A	B	C	D	E	F	G	H
1
2		1/1/24	a1	b1		1/1/23	a3	b3
3		1/2/24	a2	b2		2/1/23	a4	b4
4		1/1/23	a3	b3		2/1/35	a5	b5
5		2/1/23	a4	b4
6		2/1/35	a5	b5
7
8
9
Sheet1

Cell Formulas
Range	Formula
F2:H4	F2	=FILTER(B2:D10,(B2:B10<DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))*(B2:B10<>""))
Dynamic array formulas.

 

[Alex Blakenburg]

Based on this I would have thought ">="

only return results for anything in the last 365 days

 

[etaf]

Based on this I would have thought ">="

Opps YES - thanks for pointing that out - my mistake

 

[Fluff]

Another option based on 1year rather than 365 days

=FILTER(B2:D4,B2:B4>EDATE(TODAY(),-12))

 

[garypea123]

Thanks people - Going to take a look at these today as spreadsheet day

Will mark as solution when I have gone through, but thank you for your help as usual

 

[Fluff]

Glad we could help & thanks for the feedback.