将日期时间字符串转换为有效的日期值和有效时间值。

This code is returning as an invalid date for conversion:

此代码作为无效的转换日期返回:

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

“2014年9月10日星期三早上6:53”

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.

VBA IsDate函数用于检查表达式是否为日期，或者表达式是否可以转换为有效的日期或时间。函数返回true或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

3 个解决方案

#1

You would change:

你会改变:

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

To:

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

#2

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

去掉“星期三”，然后试试:

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

#3

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)

#1