Text format to date/month/year on excelSolved/Closed

Question

Hello, 

I have a huge list of data that has multiple dates/year along with different time format. 
I want to delete the time and obtain only the date/month/year format on excel. How do I change this? The initial data was on text format from notepad which I've segregated to different columns now on Excel.
This is how my format looks on excel Jun 19 2006 11:18AM. Can someone please help me asap?

Regards,
Cheryl

RWomanizer · Accepted Answer

Go to Data Tab

Select Text to Column,

Then check fixed with and then next

now click between date and time , it makes a new column for time and you can skip this column by select next than skip,

now click on finish.

Cheers! you have done it. :)

RWomanizer · Answer

Could you please upload a sample EXCEL file WITH sample data on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc 
AND post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too. 


Note: your data need not be to be real data but a good representative of how data looks like

Schlunggi · Answer

I tried this macro, but I must be missing some necessary changes, because all I get in the date colon is #NAME?

My dates are European DD-MM-YYYYY and need to change to ISO YYYY-MM-DD
(to get proper sorting by date)


Public Sub doToXLDate()

   Dim sTgtSheet        As String      'sheet to operate on
   Dim iTgtCol          As Integer     'column to operate on
   Dim lStartRow        As Long        'starting row from where to operate on
   Dim lEndRow          As Long        'last to row operate on
   Dim lMaxCol          As Long        'last column in use0
     
   sTgtSheet = "Sheet1"
   lStartRow = 2
   iTgtCol = 1
     
   With Sheets(sTgtSheet)
      .AutoFilterMode = False
      lEndRow = getItemLocation("*", .Cells)
      If lEndRow < lStartRow Then Exit Sub
      lMaxCol = getItemLocation("*", .Cells, bFindRow:=False)
      If (lMaxCol < iTgtCol) Then Exit Sub
        
      With Application
         .ScreenUpdating = False
         .EnableEvents = False
         .Calculation = xlCalculationManual
      End With
        
      With .Range(.Cells(lStartRow, lMaxCol + 1), .Cells(lEndRow, lMaxCol + 1))
         .NumberFormat = "general"
         .FormulaR1C1 = "=getXLDate(RC" & iTgtCol & ")"
         Application.CutCopyMode = False
         .Copy
      End With
      With .Range(.Cells(lStartRow, iTgtCol), .Cells(lEndRow, iTgtCol))
         .PasteSpecial xlPasteValues
         Application.CutCopyMode = False
         .NumberFormat = "YYYY-Mm-dd"
      End With
      .Range(.Cells(lStartRow, lMaxCol + 1), .Cells(lEndRow, lMaxCol + 1)).ClearContents
        
      With Application
         .EnableEvents = True
         .Calculation = xlCalculationAutomatic
         .Calculate
         .ScreenUpdating = True
      End With
   End With
   
End Sub

Public Function getXLDate(sInstring As String) As Variant

   If (IsDate(sInstring)) _
   Then
      getXLDate = CDate(sInstring)
   Else
      getXLDate = sInstring
   End If
     
End Function

Public Function getItemLocation(sLookFor As String, _
                                rngSearch As Range, _
                                Optional bFullString As Boolean = True, _
                                Optional bLastOccurance As Boolean = True, _
                                Optional bFindRow As Boolean = True) As Long
      
   Dim Cell             As Range
   Dim iLookAt          As Integer
   Dim iSearchDir       As Integer
   Dim iSearchOdr       As Integer
      
   If (bFullString) _
   Then
      iLookAt = xlWhole
   Else
      iLookAt = xlPart
   End If
   If (bLastOccurance) _
   Then
      iSearchDir = xlPrevious
   Else
      iSearchDir = xlNext
   End If
   If Not (bFindRow) _
   Then
      iSearchOdr = xlByColumns
   Else
      iSearchOdr = xlByRows
   End If
      
   With rngSearch
      If (bLastOccurance) _
      Then
         Set Cell = .Find(sLookFor, .Cells(1, 1), xlValues, iLookAt, iSearchOdr, iSearchDir)
      Else
         Set Cell = .Find(sLookFor, .Cells(.Rows.Count, .Columns.Count), xlValues, iLookAt, iSearchOdr, iSearchDir)
      End If
   End With
      
   If Cell Is Nothing Then
      getItemLocation = 0
   ElseIf Not (bFindRow) _
   Then
      getItemLocation = Cell.Column
   Else
      getItemLocation = Cell.Row
   End If
   Set Cell = Nothing

End Function

Text format to date/month/year on excel

3 responses

Similar discussions

Subscribe To Our Newsletter!