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>
 
Sort by date Sort by votes

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
As your source dates can be d/m or m/d dates such as 1/3 could easily be misinterpreted by any code.

Other than that, I think this should work with all possible formats.

Code: 
Sub FlowchartPunchedTape1_Click()
Dim c As Range, tmp
    Selection.NumberFormat = "general"
    Selection.Replace """", "", xlPart
For Each c In Selection
    tmp = CDate(Split(Trim(Replace(c.Value, ".", "/")), " ")(0))
        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
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
615
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,176
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