Converting date from various forms to mm/dd/yyyy format

S

Sud

New Member
Joined
Nov 22, 2011
Messages
11
Hi, guys, I am trying to convert the data in a column, "N" which is an output of another macro into mm/dd/yyyy format.

Below are the steps I am trying to perform:

1. Cell will have data in the format: 'some special characters' <space> 'Date' <space> 'timestamp or some string'
2. I have to get the 'Date' thats in the middle and convert it into "mm/dd/yyyy" format.
3. Input Dates can be of the below formats;
dd/mm/yyyy
dd/mm/yy or mm/dd/yy
d/m/yy or m/d/yy
And finally similar dates but instead of '/' we might have "-" or "."

I had written a macro which is as below:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
Sub FlowchartPunchedTape1_Click()
Dim c As Range
Dim s
Dim sFirstPart As String
Dim sSecPart As String
Dim sDate As String
Dim sTemp As String
Dim ACell As Range<o:p></o:p>
'Set a reference to the ActiveCell named ACell. You can always use
'ACell now to point to this cell, no matter where you are in the workbook.
<o:p></o:p>

For Each c In Selection
'assumes you select only entries to be processed
With c<o:p></o:p>
' replaces the special characters
c.Replace What:="""", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
c.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
c.Replace What:="-", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False<o:p></o:p>
'get only the first 9 chars
'remove leading/trailing spaces for ease of processing
Result = Trim(.Value)
sSecPart = Left(Result, 10)
sTemp = sSecPart
sFirstPart = Left(sTemp, InStrRev(sTemp, " "))
sDate = Right(sTemp, Len(sTemp) - InStrRev(sTemp, " "))
s = Split(sDate, "/")
'make sure that there are two /'s in string as check<o:p></o:p>
If UBound(s) = 2 Then
sTemp = Right("0" & s(0), 2)
s(0) = Right("0" & s(1), 2)
s(1) = sTemp<o:p></o:p>
If s(0) > 12 Then<o:p></o:p>
If s(1) > 12 Then
MsgBox "Date is not in the required format. Please check"
GoTo EXIT_SUB
End If
sTemp = s(0)
s(0) = s(1)
s(1) = sTemp<o:p></o:p>
End If

If s(0) < 9 Then
If s(1) < 9 Then
Result = Trim(.Value)
sSecPart = Left(Result, 9)
sTemp = sSecPart
sFirstPart = Left(sTemp, InStrRev(sTemp, " "))
sDate = Right(sTemp, Len(sTemp) - InStrRev(sTemp, " "))
s = Split(sDate, "/")
End If
End If
<o:p></o:p>
sDate = Join(s, "/")
End If<o:p></o:p>
'when debugged, remove the .Offset(0,1)
.Offset(0, 1).Value = sFirstPart & sDate<o:p></o:p>
End With<o:p></o:p>
Next c<o:p></o:p>
<o:p></o:p>
EXIT_SUB:
Exit Sub
End Sub
I had taken string length instead of truncating with <space> which gave me errors.
For example:

<o:p></o:p>
<TABLE style="WIDTH: 275.25pt; mso-cellspacing: 0in; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 0in 0in 0in" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=367><TBODY><TR style="HEIGHT: 15pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 156pt; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=208>Input Date<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 119.25pt; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=159>Output Date<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 156pt; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=208>1/7/10<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">
1/7/2010<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 156pt; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=208>1/7/2010<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">
1/7/2010<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 156pt; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=208>1/7/2010<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">
1/7/2010<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 4"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 156pt; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=208>1/7/2010<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">
1/7/2010<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 23.25pt; mso-yfti-irow: 5"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 156pt; PADDING-RIGHT: 0in; HEIGHT: 23.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=208> 20/07/11 11/15/42
Upda<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 23.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">07/20/11 1<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 6"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 156pt; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=208>1/7/2010<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">
1/7/2010<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 7"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 156pt; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=208>1/7/2010<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">
1/7/2010<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 8"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 156pt; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=208>1/7/2010<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">
1/7/2010<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 9"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 156pt; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=208>7/12/2010<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">
12/7/2010<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 10; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 156pt; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=208>7/12/2010<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in">
12/7/2010<o:p></o:p>​
</TD></TR></TBODY></TABLE>
<o:p> </o:p>
Suggestions please..<o:p></o:p>
<o:p> </o:p>
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
maybe this..
Code: 
Sub Button1_Click()
Range("N1:N1000").NumberFormat = ("mm\/dd\/yyyy")
End Sub
 
Upvote 0
Hi Tim,

It works for only those already in the proper date format.
It won't work for those which arent.
Check the examples below:
<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 144pt; mso-width-source: userset; mso-width-alt: 7021" width=192><TBODY><TR style="HEIGHT: 23.25pt" height=31><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; WIDTH: 144pt; HEIGHT: 23.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=31 width=192> 20/07/11 11/15/42
Update</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 144pt; mso-width-source: userset; mso-width-alt: 7021" width=192><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: red; WIDTH: 144pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=192>2/15/2011</TD></TR></TBODY></TABLE>
 
Upvote 0
Try this as an alternative to your code on a test copy of your worksheet.

Code: 
Sub FlowchartPunchedTape1_Click()
Dim c As Range, tmp
For Each c In Selection
    tmp = Format(CStr(Split(Application.WorksheetFunction.Clean(Trim(Replace(c.Value, ".", "/"))), " ")(0)), "mm/dd/yyyy")
    If Not IsDate(tmp) Then
        MsgBox "The Date " & tmp & " In Cell " & c.Address(0, 0) & vbCrLf & "is not in the required format. Please check"
        Exit For
    End If
    c.Value = tmp
Next
End Sub
 
Upvote 0
Hi Jason,

Thank you for the macro.
I ran it and got the below result:

<TABLE style="WIDTH: 468pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=624><COLGROUP><COL style="WIDTH: 156pt; mso-width-source: userset; mso-width-alt: 7606" span=3 width=208><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 156pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl68 height=21 width=208>Input Date</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 156pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl68 width=208>Jason o/p Date</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 156pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl68 width=208>Expected o/p Date</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #b6dde8; WIDTH: 156pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl65 height=20 width=208>1/1/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #b6dde8; WIDTH: 156pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl65 width=208>1/1/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #b6dde8; WIDTH: 156pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl65 width=208>1/1/2010</TD></TR><TR style="HEIGHT: 30pt" height=40><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #b6dde8; WIDTH: 156pt; HEIGHT: 30pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl66 height=40 width=208> 20/07/11 11/15/42
Update</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e6b9b8; WIDTH: 156pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl69 width=208>7/11/2020</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e6b9b8; WIDTH: 156pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl69 width=208>7/20/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #b6dde8; WIDTH: 156pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl66 height=20 width=208> 20/07/11 11/15/42 Update</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e6b9b8; WIDTH: 156pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl69 width=208>7/11/2020</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e6b9b8; WIDTH: 156pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl69 width=208>7/20/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #b6dde8; WIDTH: 156pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl66 height=20 width=208>7/12/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #b6dde8; WIDTH: 156pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl66 width=208>7/12/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #b6dde8; WIDTH: 156pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl66 width=208>7/12/2010</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #b6dde8; WIDTH: 156pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl66 height=20 width=208>12/12/2012</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #b6dde8; WIDTH: 156pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl66 width=208>12/12/2012</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #b6dde8; WIDTH: 156pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl66 width=208>12/12/2012</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #b6dde8; WIDTH: 156pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl66 height=20 width=208>21/02/11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e6b9b8; WIDTH: 156pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl69 width=208>2/11/2021</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e6b9b8; WIDTH: 156pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl69 width=208>2/21/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #b6dde8; WIDTH: 156pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl66 height=20 width=208>3/1/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #b6dde8; WIDTH: 156pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl66 width=208>3/1/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #b6dde8; WIDTH: 156pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl66 width=208>3/1/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #b6dde8; WIDTH: 156pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl66 height=20 width=208>3/15/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #b6dde8; WIDTH: 156pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl66 width=208>3/15/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #b6dde8; WIDTH: 156pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl66 width=208>3/15/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #b6dde8; WIDTH: 156pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl66 height=20 width=208>15/03/11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e6b9b8; WIDTH: 156pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl69 width=208>3/11/2015</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e6b9b8; WIDTH: 156pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl69 width=208>3/15/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #b6dde8; WIDTH: 156pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl66 height=20 width=208>20/07/11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e6b9b8; WIDTH: 156pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl69 width=208>7/11/2020</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e6b9b8; WIDTH: 156pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl69 width=208>7/20/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #b6dde8; WIDTH: 156pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl66 height=20 width=208>" 7/12/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #b6dde8; WIDTH: 156pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl66 width=208>7/12/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #b6dde8; WIDTH: 156pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl66 width=208>7/12/2010</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #b6dde8; WIDTH: 156pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl66 height=20 width=208>20/07/11 11/15/42 5:45:44</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e6b9b8; WIDTH: 156pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl69 width=208>7/11/2020</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e6b9b8; WIDTH: 156pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl69 width=208>7/20/2011</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #b6dde8; WIDTH: 156pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=21 width=208> 7/12/2010</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #b6dde8; WIDTH: 156pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl67 width=208>7/12/2010</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #b6dde8; WIDTH: 156pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl67 width=208>7/12/2010</TD></TR></TBODY></TABLE>

I am now considering the date as string and take care of the the errors (Marked in red above).
 
Upvote 0
Using your Input Date list to test the code, I get the expected results, it could be a regional, or even cell formatting issue.

See if this cures it

Code: 
Sub FlowchartPunchedTape1_Click()
Dim c As Range, tmp
    Selection.Replace """", "", xlPart
    Selection.NumberFormat = "mm/dd/yyyy"
For Each c In Selection
    tmp = Format(Split(Trim(Replace(c.Value, ".", "/")), " ")(0), "mm/dd/yyyy")
        If Not IsDate(tmp) Then
            MsgBox "The Date " & tmp & " In Cell " & c.Address(0, 0) & vbCrLf & "is not in the required format. Please check"
            Exit For
        End If
    c.Value = tmp
Next
End Sub
 
Last edited:
Upvote 0
Thank you Jason. The macro worked well.

I had to ignore the Header As it was throwing exception.
And change 2 digit Years to 4. It worked like a charm.

Thanks again Jason.
Have a good one.
 
Upvote 0
I can understand the header throwing an exception, but it should work with 2 digit years as well.

Is your default (regional) date format dd/mm/yy or mm/dd/yy ?
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
616
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
920
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,507
Messages
6,179,183
Members
452,893
Latest member
denay

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top