Anomalies using the split command

[crunchie]

Hi,

I am using the split command to plit a string in specific cells in my excel sheet. An example of the string is as follows;

<TABLE style="WIDTH: 86pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=115 border=0 x:str><COLGROUP><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 86pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=115 height=17>[FONT=Arial][SIZE=2]2011-05-18[/SIZE][/FONT]</TD></TR></TBODY></TABLE>

I am recreating the date using the following code;

Sub compareDates()
 
' NOTE: When using Selection.Offset(a,b) a = vertical shift, b = horizontal shift
 
Range("H2").Select
 
Do Until Selection.Value = ""
 
 
strInput = Selection.Value
newDate = Split(strInput, "-")
 
Range("K2") = newDate(1)
Range("L2") = newDate(2)
 
 
If Range("K2").Value = Range("L2").Value Then
 
    Selection.Offset(0, 1).Value = "Error parsing date"
 
Else: Selection.Offset(0, 1).Value = newDate(1) & "/" & newDate(2) & "/" & newDate(0)
End If
 
Range("G11") = Date
 
If Selection.Offset(0, 1).Value > Range("G11").Value Then  
      Selection.Offset(0, 2).Value = "Service Not Due"
 
Else: Selection.Offset(0, 2).Value = "Service Due"
End If
 
Selection.Offset(1, 0).Select
 
Loop
 
End Sub

The new date is then in this format;

18/05/2011

The code works perfectly untill it runs into a date where the month number is the same as the day number i.e. 2nd Feb or 3rd March etc. I get the following o/p from the code;

05/05

Which is incorrect. It should be;

05/05/2011

Is there any way of preventing this error from happening? Ive tried to put in an if statement but it is not catching the error.

Any suggestions would be greatly appreciated.

Thanks in advance.

 

[kpark91]

I'm not too sure so make a copy before you run this macro
Replace this code

If Range("K2").Value = Range("L2").Value Then
 
    Selection.Offset(0, 1).Value = "Error parsing date"
 
Else: Selection.Offset(0, 1).Value = newDate(1) & "/" & newDate(2) & "/" & newDate(0)
End If

with

Selection.Offset(0, 1).Value = newDate(1) & "/" & newDate(2) & "/" & newDate(0)

 

[Richard Schollar]

To get the value of the cell as it appears, use the Text property rather than the Value property eg:

strInput = Selection.Text

rather than:

strInput = Selection.Value

 

[shg]

How about just

mydate = datevalue(range("H2").text)