Rolling 12 months expiry dates view

[hafe4k]

Hi there,

I have a list of rows (books) around 1500 records - each book has a date they need to be checked and audited to ensure the conditions of the book is up to scratch. Some books need this reviewing every 3 months, 6 months , some 12 months and some 24 months. The key field is 'review by' date - this date is in the future and is manually updated by somebody.

Currently its a really manual task in checking through the list using filters.

I would like to create an easy to see report or some sort which will easily show me a list of:

In the next 3 months these books are due:
In the next 6 months these:
In the next 12 months these books:
and these are books that have over 12 months left. see an easy way

What would be the best approach please

Thanks in advance.

 

[JamesCanale]

Office 365?

MrExcelPlayground2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Book	Review By Date		3 months			6 months			12 months			Greater than 12 months
2	Book 1	5/1/2021		Book	Review By Date		Book	Review By Date		Book	Review By Date		Book	Review By Date
3	Book 2	5/14/2021		Book 1	5/1/2021		Book 1	5/1/2021		Book 1	5/1/2021		Book 23	5/2/2022
4	Book 3	5/16/2021		Book 2	5/14/2021		Book 2	5/14/2021		Book 2	5/14/2021		Book 24	5/29/2022
5	Book 4	6/9/2021		Book 3	5/16/2021		Book 3	5/16/2021		Book 3	5/16/2021		Book 25	6/4/2022
6	Book 5	6/22/2021		Book 4	6/9/2021		Book 4	6/9/2021		Book 4	6/9/2021		Book 26	6/24/2022
7	Book 6	7/20/2021		Book 5	6/22/2021		Book 5	6/22/2021		Book 5	6/22/2021		Book 27	7/1/2022
8	Book 7	8/13/2021		Book 6	7/20/2021		Book 6	7/20/2021		Book 6	7/20/2021		Book 28	7/20/2022
9	Book 8	9/4/2021					Book 7	8/13/2021		Book 7	8/13/2021		Book 29	7/23/2022
10	Book 9	10/3/2021					Book 8	9/4/2021		Book 8	9/4/2021		Book 30	7/26/2022
11	Book 10	10/28/2021					Book 9	10/3/2021		Book 9	10/3/2021		Book 31	8/10/2022
12	Book 11	11/12/2021								Book 10	10/28/2021		Book 32	8/23/2022
13	Book 12	11/27/2021								Book 11	11/12/2021		Book 33	9/15/2022
14	Book 13	11/30/2021								Book 12	11/27/2021		Book 34	9/25/2022
15	Book 14	12/21/2021								Book 13	11/30/2021		Book 35	10/17/2022
16	Book 15	1/4/2022								Book 14	12/21/2021		Book 36	10/28/2022
17	Book 16	1/19/2022								Book 15	1/4/2022		Book 37	10/28/2022
18	Book 17	1/28/2022								Book 16	1/19/2022		Book 38	11/6/2022
19	Book 18	2/4/2022								Book 17	1/28/2022		Book 39	12/1/2022
20	Book 19	2/18/2022								Book 18	2/4/2022		Book 40	12/13/2022
21	Book 20	3/1/2022								Book 19	2/18/2022		Book 41	12/19/2022
22	Book 21	3/10/2022								Book 20	3/1/2022		Book 42	12/31/2022
23	Book 22	4/4/2022								Book 21	3/10/2022		Book 43	1/22/2023
24	Book 23	5/2/2022								Book 22	4/4/2022		Book 44	2/19/2023
25	Book 24	5/29/2022
26	Book 25	6/4/2022
27	Book 26	6/24/2022
28	Book 27	7/1/2022
29	Book 28	7/20/2022
30	Book 29	7/23/2022
31	Book 30	7/26/2022
32	Book 31	8/10/2022
33	Book 32	8/23/2022
34	Book 33	9/15/2022
35	Book 34	9/25/2022
36	Book 35	10/17/2022
37	Book 36	10/28/2022
38	Book 37	10/28/2022
39	Book 38	11/6/2022
40	Book 39	12/1/2022
41	Book 40	12/13/2022
42	Book 41	12/19/2022
43	Book 42	12/31/2022
44	Book 43	1/22/2023
45	Book 44	2/19/2023
Sheet13

Cell Formulas
Range	Formula
D3:E8	D3	=FILTER(A2:B45,B2:B45<TODAY()+93,"")
G3:H11	G3	=FILTER(A2:B45,B2:B45<TODAY()+183,"")
J3:K24	J3	=FILTER(A2:B45,B2:B45<TODAY()+366,"")
M3:N24	M3	=FILTER(A2:B45,B2:B45>TODAY()+365,"")
Dynamic array formulas.

 

[hafe4k]

Thanks James for your swift response. That actually could work, however trying to test it only shows one row of data?
So from your example it only shows Row D3 and E3. D4-D8 does not show. Am I doing something incorrect?

 

[hafe4k]

Actually i've noticed it works when the fields are shown as 02/05/21 but not when its full 2021? I've ensured the cell properties is dates and correct.

 

[JamesCanale]

Did you try entering it with CTRL-SHIFT-ENTER instead of just ENTER?

 

[hafe4k]

I have done - if the date ends in 2021 it wont show - the ones without 20xx does. I have too many records to manually adjust each date. Any ideas?

 

[JamesCanale]

I don't think I understand how your date data looks. Is it just years?

Could you post some of your data?

 

[hafe4k]

Of course. So attached.
as you can see when the dates are formatted with the 2021 they don’t appear, if I manually take out the 20 and leave it as 21 then it works fine.
I have formulas calculating things too so a simple replace all won’t work.

is there a script to run on my data which will change all the 2021, 2022, 2023 to 21, 22, 23.

which I’m hoping will fix the issue. Hope it makes sense.

 

[JamesCanale]

I see what you mean - Your book - your best day is today - has the date field that looks like it's stored as text and not a date at all. That's why it is left justified instead of right justified like the dates that work. I don't think you have to take the '20' part out, I think you would only have to enter the cell and hit enter. You might try selecting all the data "review date" and under DATA and DATA TOOLS - select TEXT TO COLUMNS - and have it reformat all of you text correctly for date format.

 

[hafe4k]

I thought I had amended all fields to Date fields – but the latter steps TEXT TO COLUMNS did the trick – thanks so much! Helps us massively.

With that is there an easy way to filter out duplicates? But making sure it doesn’t take out one of the same name but Medienart is different.

So in this example the highlighted – ideally I would want to see the duplicate books one: e.g:



‘The Shaurya Unbound-Tales of | Hardcover

‘The Shaurya Unbound-Tales of | Softcover

… other books list



40 Years with Abdul Kalam-Untold Stories | Hardcover

40 Years with Abdul Kalam-Untold Stories | Softcover

40 Years with Abdul Kalam-Untold Stories | eBook

… other books list