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

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


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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