string convertion to date problem
 

I would appreciate your help !

I have a string which I convert to date (dd/mm/yyyy), the problem is I
can´t validate that date to check if it is correct.

1) For example, in cell A1 I have the string: 020505

2) With this macro I convert it to date:

ActiveCell.FormulaR1C1 = _

"=DATE(VALUE(RIGHT(RC[-1],2))+2000,VALUE(MID(RC[-1],3,2)),VALUE(LEFT(RC[-1],2)))"

3) In this example, the output is 02/05/05, which is correct.

MY PROBLEM: if my string is 334455 my fórmula returns 02/09/2058
It seems that the formula always returns a date, even in this case
where day is 33 and month is 44.

How can I validate to check if my string can be converted to a correct
date? The solution must consider that some months have 30 days and
others 31 !

Thank you for your help !

string convertion to date problem
 

You can use the inputbox function with option 8 like this:

Dim rngSelection As Range
Set rngSelection = Application.InputBox( _
Prompt:="Please select a range.", _
Type:=8)

this puts a range as the
HTH

Philip

"Rolo" wrote:

I would appreciate your help !

I have a string which I convert to date (dd/mm/yyyy), the problem is I
can´t validate that date to check if it is correct.

1) For example, in cell A1 I have the string: 020505

2) With this macro I convert it to date:

ActiveCell.FormulaR1C1 = _

"=DATE(VALUE(RIGHT(RC[-1],2))+2000,VALUE(MID(RC[-1],3,2)),VALUE(LEFT(RC[-1],2)))"

3) In this example, the output is 02/05/05, which is correct.

MY PROBLEM: if my string is 334455 my fórmula returns 02/09/2058
It seems that the formula always returns a date, even in this case
where day is 33 and month is 44.

How can I validate to check if my string can be converted to a correct
date? The solution must consider that some months have 30 days and
others 31 !

Thank you for your help !

string convertion to date problem
 

Sorry Philip, I can´t understand your solution...

Can anybodyelse help me ?

string convertion to date problem
 

sorry, I answered the wrong question!

"Rolo" wrote:

Sorry Philip, I can´t understand your solution...

Can anybodyelse help me ?

string convertion to date problem
 

try something like this (may be over the top tho):

CODE

Sub test()
Dim sTestStr As String
Dim dhDaysInMonth

Range("b1").Activate

Do Until ActiveCell.Offset(0, -1).Text = ""
sTestStr = ActiveCell.Offset(0, -1).Text
If VBA.IsDate(CDate(sTestStr)) Then
If VBA.Mid(sTestStr, 3, 2) <= 12 Then ' check the month
' days in that month
dhDaysInMonth = DaysInMonth(VBA.Right(sTestStr, 2) + 2000,
VBA.Mid(sTestStr, 3, 2))
If CInt(VBA.Left(sTestStr, 2)) < dhDaysInMonth Then
' its a good date
ActiveCell.FormulaR1C1 = _

"=DATE(VALUE(RIGHT(RC[-1],2))+2000,VALUE(MID(RC[-1],3,2)),VALUE(LEFT(RC[-1],2)))"
Else
ActiveCell.Value = "Bad no. of days"
End If
Else
ActiveCell.Value = "Bad no. of months"
End If
Else
ActiveCell.Value = "not a date"
End If
ActiveCell.Offset(1, 0).Select
Loop

End Sub

Function DaysInMonth(ByVal iYear, ByVal iMonth) As Integer

Dim lngDayCount As Long
Dim datDate

'Get the first of the month
datDate = DateSerial(iYear, iMonth, 1)
'Get the difference between the first and last days
lngDayCount = DateDiff("d", datCurDate, DateAdd("m", 1, datCurDate))
DaysInMonth = lngDayCount

End Function
<<<< END CODE

HTH

Philip

"Rolo" wrote:

I would appreciate your help !

I have a string which I convert to date (dd/mm/yyyy), the problem is I
can´t validate that date to check if it is correct.

1) For example, in cell A1 I have the string: 020505

2) With this macro I convert it to date:

ActiveCell.FormulaR1C1 = _

"=DATE(VALUE(RIGHT(RC[-1],2))+2000,VALUE(MID(RC[-1],3,2)),VALUE(LEFT(RC[-1],2)))"

3) In this example, the output is 02/05/05, which is correct.

MY PROBLEM: if my string is 334455 my fórmula returns 02/09/2058
It seems that the formula always returns a date, even in this case
where day is 33 and month is 44.

How can I validate to check if my string can be converted to a correct
date? The solution must consider that some months have 30 days and
others 31 !

Thank you for your help !