IF cells in column A contains a dates that are 7 days before todays date then output the date and the value in the coulumn next to it in a list

[Sifuevs]

I have a vehicle compliance sheet. Column A contains dates of next vehicle inspection. Column B contains vehicle registration numbers. I am looking for a formula that will look up dates in column A that are 7 days prior to todays date and then output a list of these dates and the corresponding vehicle reg in column B.

When returning the values i would like this list also to be in date order from nearest date to today and so forth.

Additionally to this i would like the same formula as above but column A contains mileage and instead i would like to look up mileage and base the results on 500 miles less than the mileage in column A.

I hope this makes sense. I have basic Excel knowledge but this is a challenge for me.

Thanks so much in advance for any help.

 

[etaf]

what version of excel are you using

filter will work

=SORT(FILTER(A2:B29,A2:A29<TODAY()+7))

i'm assuming prior means in advance
so totday is 6th dec
and so 7 days is the 13th

so list is from 13th dec backwards showing dates before 13th

=SORT(FILTER(A2:B29,A2:A29<TODAY()+7))

Book2
	A	B	C	D	E	F
1
2	12/1/23	A1			12/1/23	A1
3	12/2/23	A2			12/2/23	A2
4	12/3/23	A3			12/3/23	A3
5	12/4/23	A4			12/4/23	A4
6	12/5/23	A5			12/5/23	A5
7	12/6/23	A6			12/6/23	A6
8	12/7/23	A7			12/7/23	A7
9	12/8/23	A8			12/8/23	A8
10	12/9/23	A9			12/9/23	A9
11	12/10/23	A10			12/10/23	A10
12	12/11/23	A11			12/11/23	A11
13	12/12/23	A12			12/12/23	A12
14	12/13/23	A13
15	12/14/23	A14
16	12/15/23	A15
17	12/16/23	A16
18	12/17/23	A17
19	12/18/23	A18
20	12/19/23	A19
21	12/20/23	A20
22	12/21/23	A21
23	12/22/23	A22
24	12/23/23	A23
25	12/24/23	A24
26	12/25/23	A25
27	12/26/23	A26
28	12/27/23	A27
29	12/28/23	A28
Sheet1

Cell Formulas
Range	Formula
E2:F13	E2	=SORT(FILTER(A2:B29,A2:A29<TODAY()+7))
Dynamic array formulas.

mileage - is + or - 500 from a number you enter
=FILTER(A2:B22,(A2:A22>D2-500)*(A2:A22<D2+500))

Book2
	A	B	C	D	E	F
1
2	10000	a1		11750	11500	a7
3	10250	a2			11750	a8
4	10500	a3			12000	a9
5	10750	a4
6	11000	a5
7	11250	a6
8	11500	a7
9	11750	a8
10	12000	a9
11	12250	a10
12	12500	a11
13	12750	a12
14	13000	a13
15	13250	a14
16	13500	a15
17	13750	a16
18	14000	a17
19	14250	a18
20	14500	a19
21	14750	a20
22	15000	a21
Sheet2

Cell Formulas
Range	Formula
E2:F4	E2	=FILTER(A2:B22,(A2:A22>D2-500)*(A2:A22<D2+500))
Dynamic array formulas.