Convert string to date?

[tskogstrom]

Hi helpful mates,

I have a string in a cell, SK0204-167
0204 means year 2002 month april (04)

I need to find out if the cell represend BEFORE 200204 (SK0203-167
should give TRUE")
I guess if a cell beside could show 2002-04 as dateformat YYYY-MM, I
could just use > = < etc.

But how? Using a MID() function in the cell and added following
function upon it, but am stucked.


Function ConvertTextToValue(str As String)
'*************************************************
ConvertTextToValue = str * 1
End Function

 

[Guest]

It isn't necessary to convert to date! String being in A1
=MID(A1,3,4)<"0204"
does the job.

Regards,
Stefi

„tskogstrom†ezt írta:

 

[papou]

Hello
In addition to Stefi's answer, if you still want to check dates:
MsgBox DateValue(1 & "/" & Mid(ActiveCell, 5, 2) & "/" & Mid(ActiveCell, 3,
2)) < DateValue("01/02/2004")

(Please note my short date format is DD/MM/YY as per regional settings)

HTH
Cordially
Pascal

 

[Roger Govier]

Hi

If you want to convert the value in a cell to a date, then you could use
something like

Function DateConv(str As String)
Dim convdate As Date
convdate = "01/" & Mid(str, 5, 2) & "/" & Mid(str, 3, 2)
DateConv = convdate
End Function

In B1 =IF(DateConv(A1)<=DATE(2002,4,1),"do something","do something else")

 

[tskogstrom]

Thank you all,

I wantetd to use excels handling of Y2K solution - 2901 = 2029 and 30
= 1930 (future safe code, you know..

I used following to solve my needs, as documentation to any who have
similar needs later on:

Function ConvertText2YearFormat(str)
str = Format(DateValue(Mid(str, 3, 2) & "-" & Mid(str, 5, 2) & "-" &
"01"), "yyyy-mm-dd") 'Doesn't work: DateValue("12/15/1945") syntax...
Dim str2 As String
str2 = Format(DateValue("03-01-01"), "yyyy-mm-dd")
ConvertText2YearFormat = str > str2
End Function

Somehow, I got problems when I didn't used international standard yyyy-
mm-dd syntax, as my national settings are set to.

Kind reagrds
Tskogstrom

 

[Andrew Taylor]

The DATEVALUE() function is dangerous because it depends on regional
settings.(e,.g =DATEVALUE("31/01/07") returns 31 Jan with UK settings
but #VALUE! with US settings.)

It's safer to use DATE():
=DATE(2000+Mid(ActiveCell,3, 2),Mid(ActiveCell,5, 2),1)