Extracting a date from a text string

[dkmoreland]

I have a text string in an Excel spreadsheet that I am trying to split into a start date and an end date and store them in the start_date and end_date fields in an Access table.

The string looks like this and is always in the A7 cell of this spreadsheet:
From: 3/2/2018 To: 3/8/2018

I am using this code to extract the characters that comprise the date:

    dStart_date = Mid(Range("'Sheet1'!A7").Value, 7, 10)
    dEnd_date = Mid(Range("'Sheet1'!A7").Value, 21, 11)

The problem will come in when the month and/or the day are two digits - I've already run into a data type mismatch issue trying to leave room for additional date characters.

Can anyone suggest a better way to do this?

Thanks in advance.

 

[1268]

I have a text string in an Excel spreadsheet that I am trying to split into a start date and an end date and store them in the start_date and end_date fields in an Access table.

The string looks like this and is always in the A7 cell of this spreadsheet:
From: 3/2/2018 To: 3/8/2018

I am using this code to extract the characters that comprise the date:

    dStart_date = Mid(Range("'Sheet1'!A7").Value, 7, 10)
    dEnd_date = Mid(Range("'Sheet1'!A7").Value, 21, 11)

The problem will come in when the month and/or the day are two digits - I've already run into a data type mismatch issue trying to leave room for additional date characters.

Can anyone suggest a better way to do this?

Thanks in advance.

Dstart = format(Range("'Sheet1'!A7").Value, 7, 10),"mm/dd/yyyy")

Dstart = mid(dstart,7,10)

Dstart needs to be a string...

But if you just want the year why not right(yourdate,2)....

Sent from my SM-G950U using Tapatalk

 

[isladogs]

In Access, I would deal with this as follows

a) start date
Get the position of the string "To" and use that to calculate the length of the date field

Mid([DateText],7,InStr([DateText]," To")-1)-7)

b) end date - just pull everything after 'To: '

Mid([DateText],InStr([DateText],"To: ")+1)

Either do something similar in Excel or import to Access and manipulate it there

EDIT: I'd also recommend using mm/dd/yyyy or your regional equivalent for all dates

 

[Ranman256]

Sub DateFlip()

vDat = Range("A7").Value
i = InStr(vDat, "To:")
vStart = Trim(Mid(vDat, 6, i - 6 - 1))
vEnd = Trim(Mid(vDat, i + 3))

vDat = vStart
Range("b7").Select
GoSub PostDate

vDat = vEnd
Range("c7").Select
GoSub PostDate

Exit Sub

PostDate:
i = InStr(vDat, "/")
j = InStrRev(vDat, "/")
m = Left(vDat, i - 1)
d = Mid(vDat, i + 1, j - i - 1)
y = Right(vDat, 4)

ActiveCell.Value = d & "/" & m & "/" & y
Return
End Sub

 

[dkmoreland]

In Access, I would deal with this as follows

a) start date
Get the position of the string "To" and use that to calculate the length of the date field

Mid([DateText],7,InStr([DateText]," To")-1)-7)

b) end date - just pull everything after 'To: '

Mid([DateText],InStr([DateText],"To: ")+1)

Either do something similar in Excel or import to Access and manipulate it there

EDIT: I'd also recommend using mm/dd/yyyy or your regional equivalent for all dates

I'm trying to use this:

dStart_date = Mid(Range("A7").value,7,InStr(Range("A7").value," To")-1)-7)

But I'm getting a compile error that says Expected: End of statement. Is there a typo that you can see that I am missing?

Thanks

 

[dkmoreland]

Sub DateFlip()

vDat = Range("A7").Value
i = InStr(vDat, "To:")
vStart = Trim(Mid(vDat, 6, i - 6 - 1))
vEnd = Trim(Mid(vDat, i + 3))

vDat = vStart
Range("b7").Select
GoSub PostDate

vDat = vEnd
Range("c7").Select
GoSub PostDate

Exit Sub

PostDate:
i = InStr(vDat, "/")
j = InStrRev(vDat, "/")
m = Left(vDat, i - 1)
d = Mid(vDat, i + 1, j - i - 1)
y = Right(vDat, 4)

ActiveCell.Value = d & "/" & m & "/" & y
Return
End Sub

Maybe I'm not understanding the flow of this routine - I have a couple of questions.

Am I supposed to call this sub from another? Do I need to pass it any parameters? What does it return? What is the significance of the references to cells b7 and c7? The string in question is always in A7 and only occurs once.

Thanks for the clarification.

 

[isladogs]

You noticed I said if it were Access then ...

I rarely use Excel these days so I'm not the best person to answer this

 

[jdraw]

dStart_date = Mid(Range("A7").value,7,InStr(Range("A7").value," To")-1)-7)

Your brackets do not match.

 

[dkmoreland]

You noticed I said if it were Access then ...

I rarely use Excel these days so I'm not the best person to answer this

I'm using Access VBA to extract this data from an Excel spreadsheet and store it in a table.

 

[isladogs]

Jdraw has correctly pointed out the bracketing error - 1 too many ")"

Are you using the Excel spreadsheet as a linked table?
If so use the field name & record ID instead of Range("A7")

 

[The_Doc_Man]

Parentheses are unbalanced.

I do this test by writing the string with parenthesis count starting at 0, add one for every left parenthesis, subtract one for every right parenthesis. Should end up at 0. Your string doesn't end that way.

dStart_date = Mid(Range("A7").value,7,InStr(Range("A7").value," To")-1)-7)
0................1.....2....1..............2.....3....2............1..0..[COLOR="Red"]-1[/COLOR]

I'm getting a compile error that says Expected: End of statement. Is there a typo that you can see that I am missing?

The Mid function "officially" ended when the parenthesis balance reached 0, but there was something after it, so that became part of a continuing expression. Then that last parenthesis screwed the pooch because it was unbalanced.

 

[dkmoreland]

Jdraw has correctly pointed out the bracketing error - 1 too many ")"

Are you using the Excel spreadsheet as a linked table?
If so use the field name & record ID instead of Range("A7")

I saw the extra bracket after I posted.

I'm not using the spreadsheet as a linked table - I'm just opening it, pulling data out of it and closing it. The spreadsheet name changes weekly.

Now I am getting a type mismatch error on this modified statement:

strStart = Mid(Range("A7").Value, 7, InStr(Range("A7").Value, " To") - 1) - 7

Initally, I thought it was because I was trying to put a text string into a variable I had declared as a date. So I changed the variable to a string but I'm still getting the type mismatch.

Suggestions?

 

[dkmoreland]

I just realized that InStr() returns a postional number, not a string. That's probably where my type mismatch is coming from.

 

[isladogs]

I just realized that InStr() returns a postional number, not a string. That's probably where my type mismatch is coming from.

Correct!
For example, in this modified code from my first reply (MINUS the extra right bracket

Mid([DateText],7,InStr([DateText]," To")-1-6)

This starts at the 7th character, looks for the position of the string " To"
If that position is say 15, the code becomes:

Mid([DateText],7,15-1-6)

I'll leave you to work out why that's done like that
BUT its equivalent to:

Mid([DateText],7,8)

which gets e.g. 3/2/2018

If using 03/02/2018 format, which I recommend, it would be

Mid([DateText],7,10)

And of course if you use the longer version the dates will ALWAYS have 10 characters so you can just use the last code above

 

[dkmoreland]

Thanks, Colin, for the explanation. That makes perfect sense. Everything is working the way I want it to now, so I appreciate your help very much.

I appreciate the help from everyone that offered insight. I'm marking this one solved.

Diana