Most Recent Date

zinah

Active Member
Joined
Nov 28, 2018
Messages
353
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have list of employees with their goals, each employee has at least 3 goals and each goal has a different update goal date. My question is how to figure out the most recent update date of each employee? What is the right formula?
Below is a sample of employees list:

EE ID​
Last Modified DateMost Recent Update Date Per Employee
1111​
6/10/2020
1111​
6/10/2020
1111​
6/10/2020
1111​
6/10/2020
1111​
7/13/2020
1111​
6/10/2020
2222​
2/28/2020
2222​
7/18/2020
2222​
7/18/2020
2222​
7/18/2020
2222​
7/18/2020
2222​
7/18/2020
2222​
7/18/2020
2222​
7/18/2020
3333​
2/26/2020
3333​
2/26/2020
3333​
2/26/2020
3333​
2/26/2020
3333​
2/26/2020
3333​
2/26/2020
4444​
6/25/2020
4444​
6/25/2020
4444​
6/25/2020
4444​
6/25/2020
4444​
6/25/2020
4444​
6/25/2020
4444​
6/25/2020
4444​
6/25/2020
4444​
6/25/2020
5555​
6/22/2020
5555​
6/22/2020
5555​
6/22/2020
5555​
6/22/2020
5555​
6/22/2020
5555​
6/22/2020


Thank you!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
with Power Query
EE IDDate
111113/07/2020
222218/07/2020
333326/02/2020
444425/06/2020
555522/06/2020

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"EE ID", type text}, {"Last Modified Date", type date}}),
    Group = Table.Group(Type, {"EE ID"}, {{"Date", each List.Max([Last Modified Date]), type date}})
in
    Group
 
Upvote 0
Using Microsoft 365

Book1
ABCDE
1EE IDLast Modified DateUNIQUE IDMost Recent Update Date Per Employee
211116/10/2011117/13/20
311116/10/2022228/8/20
411116/10/2033332/26/20
511116/10/2044446/25/20
611117/13/2055556/24/20
711116/10/20
822222/28/20
922227/18/20
1022227/18/20
1122228/8/20
1222227/18/20
1322227/18/20
1422227/18/20
1522227/18/20
1633332/26/20
1733332/26/20
1833332/26/20
1933332/26/20
2033332/26/20
2133332/26/20
2244446/25/20
2344446/25/20
2444446/25/20
2544446/25/20
2644446/25/20
2744446/25/20
2844446/25/20
2944446/25/20
3044446/25/20
3155556/22/20
3255556/22/20
3355556/24/20
3455556/22/20
3555556/22/20
3655556/22/20
Sheet1
Cell Formulas
RangeFormula
D2:D6D2=SORT(UNIQUE(A2:A36))
E2:E6E2=IF(D2="","",MAXIFS($B$2:$B$36,$A$2:$A$36,D2#))
Dynamic array formulas.
 
Upvote 0
with Power Query
EE IDDate
111113/07/2020
222218/07/2020
333326/02/2020
444425/06/2020
555522/06/2020

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"EE ID", type text}, {"Last Modified Date", type date}}),
    Group = Table.Group(Type, {"EE ID"}, {{"Date", each List.Max([Last Modified Date]), type date}})
in
    Group
Thank you so much, I wish I'm that expert with Power Query to use your logic but I'm not YET ;), I'll keep this in future to use it, thank you once again for your input and help!
 
Upvote 0
Using Microsoft 365

Book1
ABCDE
1EE IDLast Modified DateUNIQUE IDMost Recent Update Date Per Employee
211116/10/2011117/13/20
311116/10/2022228/8/20
411116/10/2033332/26/20
511116/10/2044446/25/20
611117/13/2055556/24/20
711116/10/20
822222/28/20
922227/18/20
1022227/18/20
1122228/8/20
1222227/18/20
1322227/18/20
1422227/18/20
1522227/18/20
1633332/26/20
1733332/26/20
1833332/26/20
1933332/26/20
2033332/26/20
2133332/26/20
2244446/25/20
2344446/25/20
2444446/25/20
2544446/25/20
2644446/25/20
2744446/25/20
2844446/25/20
2944446/25/20
3044446/25/20
3155556/22/20
3255556/22/20
3355556/24/20
3455556/22/20
3555556/22/20
3655556/22/20
Sheet1
Cell Formulas
RangeFormula
D2:D6D2=SORT(UNIQUE(A2:A36))
E2:E6E2=IF(D2="","",MAXIFS($B$2:$B$36,$A$2:$A$36,D2#))
Dynamic array formulas.
Thank you for your help, I'm using Office 365, however, I don't have the SORT & UNIQUE formula yet, is there any other alternative formula I can use?
 
Upvote 0
Thank you for your help, I'm using Office 365, however, I don't have the SORT & UNIQUE formula yet, is there any other alternative formula I can use?

I'm sorry, I know there are fairly complex formulas to accomplish this. Hopefully someone more advanced than me can help...
 
Upvote 0
I'm sorry, I know there are fairly complex formulas to accomplish this. Hopefully someone more advanced than me can help...
No worries, I tried to figure out the unique ID issue and it worked, however, when I'm trying to mimic your below formula I couldn't understand what the symbol "#" stands for?
2:E6E2=IF(D2="","",MAXIFS($B$2:$B$36,$A$2:$A$36,D2#))
 
Upvote 0
T202009a.xlsm
ABCDE
1EE IDLast Modified DateUNIQUE IDMost Recent Update Date Per Employee
2111110-Jun-20111113-Jul-20
3111110-Jun-20
4111110-Jun-20
5111110-Jun-20
6111113-Jul-20
7111110-Jun-20
4b
Cell Formulas
RangeFormula
E2E2=AGGREGATE(14,6,$B$2:$B$1200/($A$2:$A$1200=D2),1)
 
Upvote 0
I added the Array formula for the unique ID

T202009a.xlsm
DE
2111113-Jul-20
32222
4b
Cell Formulas
RangeFormula
E2E2=AGGREGATE(14,6,$B$2:$B$1200/($A$2:$A$1200=D2),1)
D2:D3D2=INDEX(A2:A36, MATCH(0, COUNTIF($A$1:A1,A2:A36), 0))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

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