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?
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?
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
@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.
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.
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
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 .
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.
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
"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
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
thank you very much