2

I've ready numerous posts here about the mismatch error but every format I try I still get the same error.

Dim calendarDate, sDate, sFinal

calendarDate = "Sun Apr 05 00:00:00 CDT 2020"
sYear = Right(calendarDate, 4)
sDay = Mid(calendarDate,9,2)
sMonth = Mid(calendarDate,5,3)

If sMonth = "Apr" Then sMonth = "04" Else sMonth = sMonth
End if

sDate = sYear & "-" & sMonth & "-" & sDay


sFinal = CDate(sDate)

I get the type mismatch CDate error but the format should work? I have also tried MM/DD/YYYY.

And I have tried sFinal = DateSerial(sYear,sMonth,SDay) also does not work. But if you don't use the variables...

sFinal = DateSerial("2020","04","05") this works. I don't understand why my SYear, SMonth, SDay would not work as they are the same numbers."

My end goal here is to subtract 14 days from the calendar date but i can't even get my variable into a date format to subtract it...so maybe there is something simpler I should be doing here?

Thank you all for any help you can give much appreciated! Long time reader, first time posting.

Kevin
  • 23
  • 4
  • See [this answer](https://stackoverflow.com/a/48243660/692942) for an explanation of why you get that error and how to fix it. Basically, you need to drop the `Sun` and `CDT` before parsing the date. – user692942 May 15 '21 at 15:49

1 Answers1

1

The simplest way to manipulate will be to use Split() as each value separated by a Space (Chr(32)).

What you end up with is an array containing each element that made up the string split by the Space character (Chr(32)), so by concatenating values back together we can construct our date-time how CDate() expects, excluding the first and sixth element which will be Sun and CDT respectively, as well as re-ordering the year in the constructed string that is passed to CDate().

Dim input: input = "Sun Apr 05 00:00:00 CDT 2020"
Dim data: data = Split(input, Chr(32))
'Ignore first and sixth element in the array and build our date value
Dim output: output = CDate(data(1) & " " & data(2) & " " & data(5)) & " " & TimeValue(data(3))
Call Wscript.Echo(output)

Output:

05/04/2020 00:00:00

Note: Result will be based on the user's regional settings as CDate() uses this while parsing a Date string.

Useful Linkss

user692942
  • 16,398
  • 7
  • 76
  • 175
  • Thank you for the information, unfortunately i am still getting the mismatch error using this method. – Kevin May 15 '21 at 16:23
  • @Kevin how are you getting a mismatch, I've just tested it based on the value in your question? Try my example code on its own does that error? – user692942 May 15 '21 at 16:32
  • 1
    Nevermind!! I did not comment out my last work. This does work for me thank you so much!!! – Kevin May 15 '21 at 16:33
  • 1
    Thank you! I just accepted it, sorry still new to this. I do not have enough reputation yet to upvote it apparently. – Kevin May 16 '21 at 13:18
  • Hello again, still struggling with this for some reason. Your logic works if the text is in quotes like your input variable...but if I use my variable calendarDate from my source data, I continue to get the mismatch error. Any ideas? I can print out both strings and they return the exact same thing it does not make sense to me. – Kevin May 17 '21 at 17:37
  • @Kevin my answer was based on the information you provided, unfortunately we can’t read minds. The likelihood will be the value from source data does not match what you assume is the output. Where does the source data come from a database, spreadsheet, text file etc.? – user692942 May 17 '21 at 17:50
  • 1
    I figured this out thanks for reaching out again. The issue was due to the script looping to a blank record. Had to check the split(0) and see what it was outputting and make sure it was skipping the last record. – Kevin May 18 '21 at 00:07
  • @Kevin you could capture that with a simple `If` statement checking if `Len(input & "") > 0`. – user692942 May 18 '21 at 07:08
  • 1
    that is probably an easier method than what i was doing thanks! I appreciate everyone's feedback on this one. – Kevin May 18 '21 at 16:01