how to delete repeating dates

[mzalikhan]

Hi all

I have got a spreadsheet with some repeating dates. for example, 1/1/2001 13:00 AM and 1/1/2001 15:00 AM.

What i want is to keep only one date data (either one).

Any idea how to do that?

Peace
Muhammad Zeeshan

 

[Steve=True]

You could create a helper column with this formula:

=DATE(YEAR(B1),MONTH(B1),DAY(B1))

This will convert the dates to just a date and remove the time.

 

[Datsmart]

This code will build a Helper Column that is then used to Delete Duplicates Dates while ignoring Times:

Sub DeleteDupeDates()
Dim Rng As Range, i As Long
Application.ScreenUpdating = False
    LR = Cells(Rows.Count, 1).End(xlUp).Row
    LC = Cells(1, Columns.Count).End(xlToLeft).Column + 1
    'Range(Cells(1, LC), Cells(LR, LC)).Select
    Range(Cells(1, LC), Cells(LR, LC)).Formula = "=INT(A1)"
    Range(Cells(1, LC), Cells(LR, LC)).NumberFormat = "mm/dd/yy;@"

Set Rng = Range(Cells(1, LC), Cells(LR, LC))
    'Cycle through helper column and Delete Duplicates
    For i = Rng.Rows.Count To 1 Step -1
      If Application.WorksheetFunction.CountIf(Rng, Cells(i, LC)) > 1 Then _
        Rows(i).EntireRow.Delete
    Next i
    'Clear Helper Column of formulae
    Range(Cells(1, LC), Cells(LR, LC)).ClearContents
Application.ScreenUpdating = True
End Sub

The Helper Column is then cleared of data at the end of the code.

 

[Peter_SSs]

I have got a spreadsheet with some repeating dates. for example, 1/1/2001 13:00 AM and 1/1/2001 15:00 AM.

They are interesting times. What does 15:00 AM mean? Usually a time of 15:00 would be 3:00 PM.

=DATE(YEAR(B1),MONTH(B1),DAY(B1))

This will convert the dates to just a date and remove the time.

FYI: If B1 indeed does contain a real date/time, a simpler way to extract just the date is
=INT(B1)

 

[mzalikhan]

Thanks peter for 15:00AM
and thanks Datsmart and steve=true too.

 

[Peter_SSs]

So, have you got a successful solution?

What version of Excel are you using?

 

[mzalikhan]

@peter

Extracted date using =INT(B1) and then using IF statement for two consecutive cells in column. Then filter the true values and delete.

Just posted another question "Maximum hourly temperature of the day". Please see if you can help.

http://www.mrexcel.com/forum/showthread.php?t=553452

Peace.

 

[Peter_SSs]

What about this question?

What version of Excel are you using?

It's just that if you are using Excel 2007 or later it has a built-in feature for removing duplicates - no need for IF formulas and helper columns.