Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm trying to automatically update the date range on the top of my report in Excel based on the date range from data I download from a text database. In the below listed macro I get a Run-time error '13': Type mismatch on PEndDt = Right(PaidDateRange, 8).

I think the Date variable, PEndDt, does not like getting a String. But I've assigned strings to Date variables in other macros! I don't now how to fix this. Help please.

Sub test()

Dim PEndDt As Date
Dim PaidDateRange As String

PaidDateRange = "PAID DATE  1/01/13 -  9/31/13"
PEndDt = Right(PaidDateRange, 8)
Range("A1") = "Report thru " & Format(PEndDt, "Long Date")

End Sub
share|improve this question

2 Answers 2

up vote 2 down vote accepted

The problem is in the string.

9/31/13 is not a valid date.

9/31/2013 is also not a valid date.

share|improve this answer
    
+1 Good Catch, I updated my post to parse the string and use DateSerial() –  Portland Runner Oct 17 '13 at 20:06
    
Thank you! Some times I get so caught up in the code that I miss simple things like the fact that there are not 31 days in September. My macro now works. –  irockyoursocks Oct 17 '13 at 21:39

You'll have to parse the string into month,day,year format

Dim itemDate as String
itemDate = Right(PaidDateRange, 8)

PEndDt = DateSerial(Right(itemDate, 2), Left(itemDate, 2), Mid(itemDate, 4, 2))

If your just putting it back into a string why convert to date?

Range("A1") = "Report thru " & Right(PaidDateRange, 8)
share|improve this answer
    
I chose to convert to a date type variable so the leading space would disappear. Because I am getting this data from a text database, there may be a space in front of the date. The easiest way to process this space out is to use a Date format variable. It turns " 9/01/13" into "9/1/2013" without any additional code. It also allows for "12/01/13" to be parsed correctly. –  irockyoursocks Oct 17 '13 at 21:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.