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
DON'T MISS
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