Find Last Date and Next Date

[dcunningham]

Hi Everyone,

I have a list of asset IDs and service dates. I'd like to find the last service date and the next service date for a specific asset. The way I tried it was to use a formula like this:

{=IF(MIN(IF(Lists!$J$11:$J$31 = 'Action Overview'!A2,Lists!$K$11:$K$31))=0,NA(), MIN(IF(Lists!$J$11:$J$31 = 'Action Overview'!A2,Lists!$K$11:$K$31)))}

This would be to find the last service date, and I'd use 'MAX' in place of 'MIN' for the next service date. The problem is that my list contains dates before the last service date and after the next service date, so I wind up with dates far in the past and far into the future. I think the way to fix this would to be to use =TODAY() to get the current date, and use logic that says "maximum date for this asset less than or equal to current date" for the last service date and "minimum date for this asset greater than or equal to current date". I just don't have the familiarity with Excel's functions to make this work.

Any help you can provide with this would be greatly appreciated.

Thanks,

Dan

 

[AhoyNC]

Something like this?
These are array formulas and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E	F
1	ID	Date		ID	Date
2	ID1	4/3/2016		ID1	12/20/2016	Last Service
3	ID2	3/18/2016			4/14/2017	Next Service
4	ID3	5/9/2016
5	ID1	7/30/2016
6	ID1	12/9/2017
7	ID6	3/5/2017
8	ID3	1/24/2017
9	ID8	1/1/2016
10	ID1	4/14/2017
11	ID3	4/15/2017
12	ID6	8/17/2016
13	ID1	5/24/2016
14	ID3	1/31/2016
15	ID14	3/28/2017
16	ID1	12/20/2016
17	ID6	1/8/2017
18	ID3	4/29/2017
19	ID6	2/5/2016
Sheet

 

[dcunningham]

Thanks AhoyNC! This did just what I needed it to do. Thanks!

Dan

 

[AhoyNC]

You're welcome. Thanks for the feedback.