# Find Max timestamp for every unique date in datetime column

I have an Excel file with 6000 rows and 100 rows for each unique date. This means that there are 60 unique dates in a datetime column with dd-mm-yyyy mm:hh format. I need to collect all unique date row when it's time is max.

Below is an example for the datetime column:

``````18-10-2018 06:31
18-10-2018 06:46
18-10-2018 06:35
19-10-2018 23:30
19-10-2018 06:31
19-10-2018 05:31
20-10-2018 06:31
20-10-2018 06:52
20-10-2018 08:48
``````

and I need each unique date row with highest timestamp like

``````18-10-2018 06:46
19-10-2018 23:30
20-10-2018 08:48
``````

• Divide the column on date and time parts (`INT(A1)` and `A1-INT(A1)`, formatted as date and time respectively). Build pivot table, with date as rows and MAX(time) as values. – Akina Oct 24 '18 at 7:59

The given task can be accomplished by using a couple of helper columns and 'Remove Duplicates'

Below I'm trying my best to represent excel spreadsheet

``````        A                       B (Helper 1)                                        C (Helper 2)                                                            D (Helper 3)                E
1                           =DATE(YEAR(A1),MONTH(A1),DAY(A1))       =MAX(IF(DATE(YEAR(\$A\$3:\$A\$6),MONTH(\$A\$3:\$A\$6),DAY(\$A\$3:\$A\$6))=B1,\$A\$3:\$A\$6))
2   DateTime in DataSet     Date from DataSet                       Max DateTime (with Formula)                                                     Max DateTime (only values)  Max DateTime (after 'Remove Duplicates')
3   10/24/2018 00:00        10/24/2018                              10/24/2018 12:52                                                                10/24/2018 12:52            10/24/2018 12:52
4   10/24/2018 12:52        10/24/2018                              10/24/2018 12:52                                                                10/24/2018 12:52            10/25/2018 12:52
5   10/25/2018 12:52        10/25/2018                              10/25/2018 12:52                                                                10/25/2018 12:52
6   10/25/2018 00:00        10/25/2018                              10/25/2018 12:52                                                                10/25/2018 12:52
``````

Here are the steps:

• Column A is the original DataSet

• Column B is Helper 1 to get corresponding 'Date' values only - Formula mentioned in top row

• Column C is Helper 2 to find MAX value in the original DataSet (in Column A) for corresponding date (in Column B) - again formula mentioned in top row

• Column D is Helper 3 which is just a copy paste of Column C (but 'Values' only)

• Column E is again copy paste of Column D and then used 'Remove Duplicates' (for Excel 2016: Under 'Data' tab -> 'Data Tools' group; same for previous versions as well, at least as far back as Excel 2007)

Hope this helps.

• great answer! instead of columns D & E, it's possible to use a pivot table. `=DATE(YEAR(A1),MONTH(A1),DAY(A1))` - `INT(A1)` just does the same (of course need to be formatted as date). – Máté Juhász Oct 24 '18 at 8:26
• Thank you @MátéJuhász ! Yes, your's seems to be a cool solution !! – Karthick Ganesan Oct 24 '18 at 9:03
• Nice solution,,, was critical to convert Timestamps into true value ,, perfect 10 ☺ – Rajesh Sinha Oct 25 '18 at 7:48