[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Converting a text string to a date

Posted on 2012-08-20
9
Medium Priority
?
334 Views
Last Modified: 2012-08-22
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
0
Comment
Question by:chtullu135
9 Comments
 
LVL 9

Expert Comment

by:Evan Cutler
ID: 38314038
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
 

Author Comment

by:chtullu135
ID: 38314075
Actually, 20120601 is located as a text formatted value from a cell in the source spreadsheet and I am using vba.
0
 

Author Comment

by:chtullu135
ID: 38314103
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 22

Accepted Solution

by:
Flyster earned 1200 total points
ID: 38314239
See if this works for you:

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

Flyster
0
 
LVL 39

Assisted Solution

by:nutsch
nutsch earned 400 total points
ID: 38314244
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
 
LVL 18

Assisted Solution

by:krishnakrkc
krishnakrkc earned 400 total points
ID: 38314794
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
 

Expert Comment

by:SmittyNet
ID: 38319022
Val = Format(yourvalue, "mm/dd/yyyy")
Val = DateAdd("d", 1, Val)
0
 

Author Comment

by:chtullu135
ID: 38320693
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
 

Author Closing Comment

by:chtullu135
ID: 38320715
I've decided to split the points.  They were great solutions.  Thanks
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question