Excel Formula to highlight most recent record

JoeRooney

Board Regular
Joined
Nov 27, 2017
Messages
175
Office Version
  1. 365
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.

PolicyTrans_TimeRequired Formula
AAA25/10/2019 02:07:42 PM
AAA06/12/2019 02:05:44 PM
AAA23/12/2019 11:01:12 AM
AAA17/01/2020 09:15:41 AMLast
BBB09/06/2020 03:18:04 PM
BBB10/07/2020 05:39:06 PM
BBB12/08/2020 01:46:49 PM
BBB10/09/2020 12:05:46 PM
BBB10/10/2020 12:07:26 PM
BBB28/11/2020 12:06:15 PMLast
CCC18/12/2017 04:37:01 PM
CCC06/03/2018 12:32:23 PMLast
 
how about
=IF(A1&B1=A1&MAXIFS($B$1:$B$12,$A$1:$A$12,A1),"last","")

Book13
ABCD
1AAA10/25/19 14:07 
2AAA12/6/19 14:05 
3AAA12/23/19 11:01 
4AAA1/17/20 9:15Lastlast
5BBB6/9/20 15:18 
6BBB7/10/20 17:39 
7BBB8/12/20 13:46 
8BBB9/10/20 12:05 
9BBB10/10/20 12:07 
10BBB11/28/20 12:06Lastlast
11CCC12/18/17 16:37 
12CCC3/6/18 12:32Lastlast
13CCC1/17/20 9:15 
Sheet1
Cell Formulas
RangeFormula
D1:D13D1=IF(A1&B1=A1&MAXIFS($B$1:$B$12,$A$1:$A$12,A1),"last","")
 
Upvote 1
how about
=IF(A1&B1=A1&MAXIFS($B$1:$B$12,$A$1:$A$12,A1),"last","")

Book13
ABCD
1AAA10/25/19 14:07 
2AAA12/6/19 14:05 
3AAA12/23/19 11:01 
4AAA1/17/20 9:15Lastlast
5BBB6/9/20 15:18 
6BBB7/10/20 17:39 
7BBB8/12/20 13:46 
8BBB9/10/20 12:05 
9BBB10/10/20 12:07 
10BBB11/28/20 12:06Lastlast
11CCC12/18/17 16:37 
12CCC3/6/18 12:32Lastlast
13CCC1/17/20 9:15 
Sheet1
Cell Formulas
RangeFormula
D1:D13D1=IF(A1&B1=A1&MAXIFS($B$1:$B$12,$A$1:$A$12,A1),"last","")
Works great
 
Upvote 0
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?
 
Upvote 0
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
ABCD
1AAA2019-10-25 43763
2AAA2019-12-06 43805
3AAA2019-12-23 43822
4AAA2020-01-17last43847
5BBB2020-06-09 43991
6BBB2020-07-10 44022
7BBB2020-08-12 44055
8BBB2020-09-10 44084
9BBB2020-10-10 44114
10BBB2020-11-28last44163
11CCC2017-12-18 43087
12CCC2018-03-06 43165
13CCC2020-01-17last43847
Sheet1
Cell Formulas
RangeFormula
C1:C13C1=IF(A1&D1=A1&MAXIFS($D$1:$D$1000,$A$1:$A$1000,A1),"last","")
D1:D13D1=DATEVALUE(RIGHT(B1,2)&"/"&(MID(B1,6,2))&"/"&LEFT(B1,4))
 
Upvote 0
Solution
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
ABCD
1AAA2019-10-25 43763
2AAA2019-12-06 43805
3AAA2019-12-23 43822
4AAA2020-01-17last43847
5BBB2020-06-09 43991
6BBB2020-07-10 44022
7BBB2020-08-12 44055
8BBB2020-09-10 44084
9BBB2020-10-10 44114
10BBB2020-11-28last44163
11CCC2017-12-18 43087
12CCC2018-03-06 43165
13CCC2020-01-17last43847
Sheet1
Cell Formulas
RangeFormula
C1:C13C1=IF(A1&D1=A1&MAXIFS($D$1:$D$1000,$A$1:$A$1000,A1),"last","")
D1:D13D1=DATEVALUE(RIGHT(B1,2)&"/"&(MID(B1,6,2))&"/"&LEFT(B1,4))
Works perfect , thank you
 
Upvote 0

Forum statistics

Threads
1,226,835
Messages
6,193,227
Members
453,781
Latest member
Buzby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top