CODEWITHSUNDEEP
vbavalidationexceldatetime
user5758156
user5758156

Reputation:

Convert datetime string to valid date value and valid time value

This code is returning as an invalid date for conversion:

'Wednesday, September 10, 2014 6:53 AM'

The VBA IsDate function is used to check if an expression is a Date or the expression can be converted to a valid Date or Time. The function returns true or false.

How can I get the date to be valid?

'-----------------------------------------

Dim x As Integer

x = ActiveCell.Row

'-----------------------------------------

Dim vDate As Variant

vDate = "Wednesday, September 10, 2014 6:53 AM"

MsgBox vDate

MsgBox IsDate(vDate) 'retuns false

'-----------------------------------------

Dim fDAT As String

fDAT = Left(vDate, Len(vDate) - 7)

MsgBox fDAT

MsgBox IsDate(fDAT) 'retuns false

'-----------------------------------------

''ActiveSheet.Range("a" & x).Value = fDAT

Upvotes: 1

Views: 347

Answers (3)

Dirk Reichel
Dirk Reichel

Reputation: 7979

to get also the correctness you can use something like that:

Sub blabla()
  Dim vDate As Variant, xDate As Boolean
  vDate = "Wednesday, September 10, 2014 6:53 AM"
  xDate = IsDate(vDate) Or (IsDate(Mid(vDate, InStr(vDate, ",") + 1)) And Left(vDate, InStr(vDate, ",") - 1) = Format(Mid(vDate, InStr(vDate, ",") + 1), "dddd"))
End Sub

this would also be false if it is not the correct weekday ;)

To get a usable value you could use something like:

If Not IsDate(vDate) And IsDate(Mid(vDate, InStr(vDate, ",") + 1)) Then vDate = Mid(vDate, InStr(vDate, ",") + 1)

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152585

You would change:

fDAT = Left(vDate, Len(vDate) - 7)

To:

fDAT = Mid(vDate, InStr(vDate, ",") + 2)

Upvotes: 1

Drew
Drew

Reputation: 16

Get rid of the "Wednesday, " and try something like:

vDate = Format(vDate, "M/D/YYYY H:MM:SS AM/PM")
xDate = IsDate(vDate)

Upvotes: 0

Related Questions