0

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

please help me

1
  • 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
2

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.

3
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.