Excel Formula to highlight most recent record

[JoeRooney]

Hi All,

I need a formula to highlight the most recent record for a policy , I have included a example below.

Any help with this is greatly appreciated.

Policy	Trans_Time	Required Formula
AAA	25/10/2019 02:07:42 PM
AAA	06/12/2019 02:05:44 PM
AAA	23/12/2019 11:01:12 AM
AAA	17/01/2020 09:15:41 AM	Last
BBB	09/06/2020 03:18:04 PM
BBB	10/07/2020 05:39:06 PM
BBB	12/08/2020 01:46:49 PM
BBB	10/09/2020 12:05:46 PM
BBB	10/10/2020 12:07:26 PM
BBB	28/11/2020 12:06:15 PM	Last
CCC	18/12/2017 04:37:01 PM
CCC	06/03/2018 12:32:23 PM	Last

 

[etaf]

how about
=IF(A1&B1=A1&MAXIFS($B$1:$B$12,$A$1:$A$12,A1),"last","")

Book13
	A	B	C	D
1	AAA	10/25/19 14:07
2	AAA	12/6/19 14:05
3	AAA	12/23/19 11:01
4	AAA	1/17/20 9:15	Last	last
5	BBB	6/9/20 15:18
6	BBB	7/10/20 17:39
7	BBB	8/12/20 13:46
8	BBB	9/10/20 12:05
9	BBB	10/10/20 12:07
10	BBB	11/28/20 12:06	Last	last
11	CCC	12/18/17 16:37
12	CCC	3/6/18 12:32	Last	last
13	CCC	1/17/20 9:15
Sheet1

Cell Formulas
Range	Formula
D1:D13	D1	=IF(A1&B1=A1&MAXIFS($B$1:$B$12,$A$1:$A$12,A1),"last","")

 

[JoeRooney]

how about
=IF(A1&B1=A1&MAXIFS($B$1:$B$12,$A$1:$A$12,A1),"last","")

Book13
	A	B	C	D
1	AAA	10/25/19 14:07
2	AAA	12/6/19 14:05
3	AAA	12/23/19 11:01
4	AAA	1/17/20 9:15	Last	last
5	BBB	6/9/20 15:18
6	BBB	7/10/20 17:39
7	BBB	8/12/20 13:46
8	BBB	9/10/20 12:05
9	BBB	10/10/20 12:07
10	BBB	11/28/20 12:06	Last	last
11	CCC	12/18/17 16:37
12	CCC	3/6/18 12:32	Last	last
13	CCC	1/17/20 9:15
Sheet1

Cell Formulas
Range	Formula
D1:D13	D1	=IF(A1&B1=A1&MAXIFS($B$1:$B$12,$A$1:$A$12,A1),"last","")

Works great

 

[etaf]

you are welcome

 

[JoeRooney]

Hi hoping someone could help, this formula only works for me if the format is a timestamp, I now need it to work off only a date in date format yyyy-mm-dd , what adjustments are required to the formula to find the most recent record based on a date. Is this possible?

 

[etaf]

is that a real date or text
right click on a date and format as general - do you see a number - or do you still see a date
if so then as its text , it will not know the latest MAX date - unless the dates are formatted as dates???

you can have a real date and still have it formatted to show yyyy-mm-dd

if you use
text to columns - next next etc
does that get converted to a real date

otherwise you would may need to add a helper column , and convert to a real date

Assuming there is NO time in that format then
IN say column D
=DATEVALUE(RIGHT(B2,2)&"/"&(MID(B2,6,2))&"/"&LEFT(B2,4))
now you need to change the formula
instead of B to reference D
=IF(A1&D1=A1&MAXIFS($D$1:$D$1000,$A$1:$A$1000,A1),"last","")

Book2
	A	B	C	D
1	AAA	2019-10-25		43763
2	AAA	2019-12-06		43805
3	AAA	2019-12-23		43822
4	AAA	2020-01-17	last	43847
5	BBB	2020-06-09		43991
6	BBB	2020-07-10		44022
7	BBB	2020-08-12		44055
8	BBB	2020-09-10		44084
9	BBB	2020-10-10		44114
10	BBB	2020-11-28	last	44163
11	CCC	2017-12-18		43087
12	CCC	2018-03-06		43165
13	CCC	2020-01-17	last	43847
Sheet1

Cell Formulas
Range	Formula
C1:C13	C1	=IF(A1&D1=A1&MAXIFS($D$1:$D$1000,$A$1:$A$1000,A1),"last","")
D1:D13	D1	=DATEVALUE(RIGHT(B1,2)&"/"&(MID(B1,6,2))&"/"&LEFT(B1,4))

 

[JoeRooney]

is that a real date or text
right click on a date and format as general - do you see a number - or do you still see a date
if so then as its text , it will not know the latest MAX date - unless the dates are formatted as dates???

you can have a real date and still have it formatted to show yyyy-mm-dd

if you use
text to columns - next next etc
does that get converted to a real date

otherwise you would may need to add a helper column , and convert to a real date

Assuming there is NO time in that format then
IN say column D
=DATEVALUE(RIGHT(B2,2)&"/"&(MID(B2,6,2))&"/"&LEFT(B2,4))
now you need to change the formula
instead of B to reference D
=IF(A1&D1=A1&MAXIFS($D$1:$D$1000,$A$1:$A$1000,A1),"last","")

Book2
	A	B	C	D
1	AAA	2019-10-25		43763
2	AAA	2019-12-06		43805
3	AAA	2019-12-23		43822
4	AAA	2020-01-17	last	43847
5	BBB	2020-06-09		43991
6	BBB	2020-07-10		44022
7	BBB	2020-08-12		44055
8	BBB	2020-09-10		44084
9	BBB	2020-10-10		44114
10	BBB	2020-11-28	last	44163
11	CCC	2017-12-18		43087
12	CCC	2018-03-06		43165
13	CCC	2020-01-17	last	43847
Sheet1

Cell Formulas
Range	Formula
C1:C13	C1	=IF(A1&D1=A1&MAXIFS($D$1:$D$1000,$A$1:$A$1000,A1),"last","")
D1:D13	D1	=DATEVALUE(RIGHT(B1,2)&"/"&(MID(B1,6,2))&"/"&LEFT(B1,4))

Works perfect , thank you

 

[etaf]

you are welcome