Cannot convert my variables to CDate or Serial Date, getting Mismatch error

user_15935458 0
Viewed 133

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.

vbscript
1 Answers

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

user_6929420
Related