Converting a text string to a date

I have a text string 20120601 that I want to treat as a date.  It comes in as
LTrim(RTrim(Irow.Columns("A").Value)).  When I try to use a date add function on it, no values are returned.  I've tried
CDate(LTrim(RTrim(Irow.Columns("A").Value)))
I'm wondering if I have to parse the string and move the year, month, and days around
chtullu135Asked:
Who is Participating?
 
FlysterConnect With a Mentor Commented:
See if this works for you:

mydate = Mid(gstrJournalDate, 5, 2) & "/" & Right(gstrJournalDate, 2) & "/" & Left(gstrJournalDate, 4)

Flyster
0
 
Evan CutlerVolunteer Chief Information OfficerCommented:
for 20120601
I assume
year 2012
Mo    06
Day 01

This moves on  yyyy, mm, dd

=DATE(MID(A3,1,4),MID(A3,5,2),MID(A3,7,2))
0
 
chtullu135Author Commented:
Actually, 20120601 is located as a text formatted value from a cell in the source spreadsheet and I am using vba.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
chtullu135Author Commented:
below is the actual code. LTrim(RTrim(Irow.Columns("A").Value))  is a value of 20120401 and is formatted as a text string.  I cannot change it on the source spreadsheet.  I need to subtract 1 day from the above value to get me the month ending date - in this case 20120331

 If LTrim(RTrim(Irow.Columns("A").Value)) = LTrim(RTrim(Format(gstrJournalDate, "yyyymmdd"))) Then
                                    lngX = rng.Row
                                        mRgInterestAccruals(lngX - lngY, 1) = Irow.Columns("G").Value
                                        If blExchangeRate Then
                                            mRgExchangeRate(lngX - lngY, 1) = gdblExchangeRate
                                        End If
                                End If

Open in new window

0
 
nutschConnect With a Mentor Commented:
Try this

Dim dtMyDate As Date, sMyDate As String
sMyDate = LTrim(RTrim(Irow.Columns("A").Value))

dtMyDate = DateSerial(CLng(Left(sMyDate, 4)), CLng(Mid(sMyDate, 5, 2)), CLng(Right(sMyDate, 2)))

 If dtMyDate = LTrim(RTrim(Format(gstrJournalDate, "yyyymmdd"))) Then
                                    lngX = rng.Row
                                        mRgInterestAccruals(lngX - lngY, 1) = Irow.Columns("G").Value
                                        If blExchangeRate Then
                                            mRgExchangeRate(lngX - lngY, 1) = gdblExchangeRate
                                        End If
                                End If
                                            

Open in new window

0
 
krishnakrkcConnect With a Mentor Commented:
Hi,

Dim sMyDate     As String
Dim dtMyDate    As Date

sMyDate = LTrim(RTrim(Irow.Columns("A").Value))

dtMyDate = Evaluate("eomonth(text(" & sMyDate & ",""0000\-00\-00""),-1)")

MsgBox dtMyDate

Open in new window


Kris
0
 
SmittyNetCommented:
Val = Format(yourvalue, "mm/dd/yyyy")
Val = DateAdd("d", 1, Val)
0
 
chtullu135Author Commented:
Hello,

I worked on it and came up with the following code which seems to work
strDate = LTrim(RTrim(Irow.Columns("A").Value))
                                strDate = Mid(strDate, 5, 2) & "/" & Right(strDate, 2) & "/" & Left(strDate, 4)
                                strDate = Format(DateAdd("d", -1, strDate), "yyyymmdd")
                                If strDate = LTrim(RTrim(Format(gstrJournalDate, "yyyymmdd"))) Then
                                    lngX = rng.Row
                                        mRgInterestAccruals(lngX - lngY, 1) = Irow.Columns("G").Value
                                        If blExchangeRate Then
                                            mRgExchangeRate(lngX - lngY, 1) = gdblExchangeRate
                                        End If
                                End If

Open in new window

0
 
chtullu135Author Commented:
I've decided to split the points.  They were great solutions.  Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.