Ask a question Report

Text format to date/month/year on excel [Solved]

Cheryl - Latest answer on Jan 4, 2013 01:57PM
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
Read more 
Answer
+8
moins plus
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. :)
Cheryl- Jun 3, 2011 05:44AM
Hello,
I already tried this but no luck as on my sheet I have the data in this format Jun 04 2009 05:21PM, whereas it should be segragated with either a / or - signs inorder to format the date as desired.

Can you please suggest how to segregate these data into DD-MM-YYYY format?

Appreciate your help on this!

Regards,
Cheryl
Reply
Zella- Jul 3, 2011 06:46PM
you are such a great dude..... i helps me lot....

thank you very much
Reply
Add comment
Answer
+2
moins plus
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
rizvisa1 4302Posts Thursday January 28, 2010Registration date ContributorStatus December 6, 2014Last seen - Dec 18, 2012 06:12PM
@Schlunggi could you please upload a sample workbook at some public file share site like speedyshare and post back link to the file back here to see the issue
Reply
Schlunggi 2Posts Tuesday December 18, 2012Registration date December 29, 2012Last seen - Dec 29, 2012 02:43AM
I have uploaded a sample File (DecStmtsNFG.xls) on Speedyshare.
I also have included on Sheet2 a different solution, but it only works if dates have leading zeros.
Reply
rizvisa1 4302Posts Thursday January 28, 2010Registration date ContributorStatus December 6, 2014Last seen - Jan 4, 2013 01:57PM
you would need to paste the link to the file back here
Reply
Add comment
Answer
+1
moins plus
Could you please upload a sample EXCEL file WITH sample data on some shared site like http://www.speedyshare.com/ , 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
Cheryl- Jun 5, 2011 03:10AM
Correct...i got these information via email on the body of the mail so I got to copy and paste them either on Notepad/excel however since they do not have a separator like this example "Apr-22-1985" I cannot convert the format type as per desired.

Please suggest
Reply
Cheryl- Jun 6, 2011 01:03AM
Is there anyone who can help me on this??? I need this urgently.

Format I have now is Sep 22 2007

Desired format I want is 22-Sep-2007
Reply
rizvisa1 4302Posts Thursday January 28, 2010Registration date ContributorStatus December 6, 2014Last seen - Jun 6, 2011 08:55AM
This code with take the date and covert it to values that excel can relate to when it comes to date. It will put the format of DD-MMM-YYYY. You can change it to what ever format you would like

1. Make a back up of your file
2. Open the file
3. Click ALT + F11 at same time to goto VBE
4.Paste the code (updates the sheet name, column to target etc to suit your need)
5. Run the macro doToXLDate


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 = 1  
   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 = "DD-Mmm-YYYY"
      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
Reply
Cheryl- Jun 8, 2011 11:12AM
Whoahhh that formula looks so complicated for me....any other formula that you can propose?

Regards,
Cheryl
Reply
rizvisa1 4302Posts Thursday January 28, 2010Registration date ContributorStatus December 6, 2014Last seen - Jun 8, 2011 06:44PM
"doToXLDate" is not a formula. It is a macro that will convert a given column into date, that you can format as you wish. All you have to do it paste the code, so I am, at lost where is the complexity in it for you. Which of the 5 step is looking giant leap ? Only thing that you need to correct is the target column and target sheet to the right values in "doToXLDate"

There are usually more than one way. It would have helped had you posted the link to the file, as was asked earlier. Yup i Know you posted a link but that is not the complete link to the file and is total useless to any one to have that link. You never reposted the link back, or at least not that i can see
Reply
Add comment
This document entitled « Text format to date/month/year on excel » from Kioskea (en.kioskea.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.

Not a member yet?

sign-up, it takes less than a minute and it's free!

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.