asked on

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

Evan Cutler

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))

Juan Velasquez

ASKER

Actually, 20120601 is located as a text formatted value from a cell in the source spreadsheet and I am using vba.

Juan Velasquez

ASKER

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

ASKER CERTIFIED SOLUTION

Flyster

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

nutsch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

krishnakrkc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SmittyNet

Val = Format(yourvalue, "mm/dd/yyyy")
Val = DateAdd("d", 1, Val)

Juan Velasquez

ASKER

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

Juan Velasquez

ASKER

I've decided to split the points. They were great solutions. Thanks