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

Question

Ask a question Report

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

Cheryl - Jun 2, 2011 at 02:38 AM - Latest reply: ExcelNewbie - Apr 22, 2016 at 05:19 PM

Excel Ask a question Report

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

Report Respond to Schlunggi

Schlunggi

Posts: 2
Registration date: Tuesday December 18, 2012
Last seen: December 29, 2012

2 Dec 18, 2012 at 04:25 AM

2

Thank you

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

Report

rizvisa1

Posts: 4475
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: January 6, 2016

917 - Dec 18, 2012 at 06:12 PM

@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

Report

Schlunggi

Posts: 2
Registration date: Tuesday December 18, 2012
Last seen: December 29, 2012

2 - Dec 29, 2012 at 02:43 AM

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.

Report

rizvisa1

Posts: 4475
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: January 6, 2016

917 - Jan 4, 2013 at 01:57 PM

you would need to paste the link to the file back here

Report Respond to RWomanizer

RWomanizer

Posts: 368
Registration date: Monday February 7, 2011
Status: Contributor
Last seen: September 30, 2013

194 Jun 3, 2011 at 06:07 AM

1

Thank you

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

View all 10 comments

Report

Cheryl - Jun 5, 2011 at 03:10 AM

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

Report

Cheryl - Jun 6, 2011 at 01:03 AM

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

Report

rizvisa1

Posts: 4475
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: January 6, 2016

917 - Jun 6, 2011 at 08:55 AM

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

Report

Cheryl - Jun 8, 2011 at 11:12 AM

Whoahhh that formula looks so complicated for me....any other formula that you can propose?

Regards,
Cheryl

Report

rizvisa1

Posts: 4475
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: January 6, 2016

917 - Jun 8, 2011 at 06:44 PM

"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

Ask a question

@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
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.
I've uploaded the sample data on the above link http://www.speedyshare.com/. Pls guide me on how to convert this to YY-MM-DD format?
Cheryl , you need to post the whole link to the file.

I was thinking that text to column would give you the date, but you are right , it is not giving in this case. So if the data is in one column, you may have to use a formula to force it to date type or use a user defined function to do so. If these dates are at random location, then I am afraid you dont have many good choices left and only option would be to use a macro.
Hello,

I did posted the entire data on the link...I'll once again post the data on the link....Can you give me a formula to work on Macro level? I'm very new to excel and do not have knowledge on Macro...
Many thanks
Hello,

Here is an example of how my format looks after pasting them on excel spreadsheet., If I do a right click to change the format it never works as it does not have a separator in between. Moreover I have no clue how to change the format in Macro level. Please suggest!

Apr 15 2006
Jun 10 2010
Mar 15 2003
Jan 01 2002
Apr 22 1979
Jul 12 1980
May 10 1985
Sep 22 2009
Oct 12 2007
Dec 17 1969
Ok correct my assumptions
1. The date are always found in one specific column, lets say column B
2. There are no merge cells on the sheet
3. All you want is to convert that one specific column to date so that it can be formatted as per you want .

Please correct my assumptions.
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
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
Whoahhh that formula looks so complicated for me....any other formula that you can propose?

Regards,
Cheryl
"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

RWomanizer · Accepted Answer · 2011-06-02T04:42:52-0400

Best answer

Report Respond to RWomanizer

RWomanizer

Posts: 368
Registration date: Monday February 7, 2011
Status: Contributor
Last seen: September 30, 2013

194 Jun 2, 2011 at 04:42 AM

8

Thank you

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. :)

Say "Thank you" 8

A few words of thanks would be greatly appreciated. Add comment

CCM 3194 users have said thank you to us this month

Report

Cheryl - Jun 3, 2011 at 05:44 AM

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

Report

Zella - Jul 3, 2011 at 06:46 PM

you are such a great dude..... I helps me lot....

thank you very much

Report

Bunnyslippers - Feb 10, 2016 at 11:56 PM

For those who haven't figured it out, "Then check fixed with and then next" should say "Then check fixed width and then click next."

Report

ExcelNewbie - Apr 22, 2016 at 05:19 PM

Thanks so much!

Audio (7,949)

Hardware (46,343)

Internet/Social Networks (32,931)

Linux/Unix (4,107)

MacOS (1,788)

Messaging (33,645)

Mobile (27,983)

Network (12,186)

Office Software (15,897)

Programming (2,469)

Software (20,856)

Video (164)

Video Games (8,261)

Viruses/Security (4,537)

Webmastering (867)

Windows (30,728)

Bugs and Suggestions (986)

Members' Cafe (1,631)

Opinions About Reviews (99)

Opinions About the News (1,175)

Ranking Software (6)

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

3 replies

Your reply