VBA将字符串转换为日期 [英] VBA Convert String to Date

问题描述

我在Excel工作表中具有以下格式的数据:

I have data in an excel sheet in the following format:

ItemCode                            DeliveryDate
5456987                              24.01.2009
5456988                                          
5456989                              12.24.2009
5456990                              12/24/2009

我已将DeliveryDate的值存储在数组中.我需要根据日期的基础做出决定，然后将结果打印在一张新纸上.所以我必须将值转换为数组:

I have stored the values of DeliveryDate in an array. I need to make decision on basics of date and then print the result in a new sheet. So I have to convert the values into array:

Dim current as Date, highest as Date, result() as Date
For Each itemDate in DeliveryDateArray
    current = CDate(itemDate)
    if current > highest then
         highest = current
    end if
    ' some more operations an put dates into result array
Next itemDate
'After activating final sheet...
Range("A1").Resize(UBound(result), 1).Value = Application.Transpose(result)

不幸的是，CDate()函数引发错误:

Unfortunately, CDate() function throws the error:

运行时错误'13':

Run-time error '13':

类型不匹配

VBA中有一个可以执行的功能:

Is there a function in VBA which can:

• 解析具有 any 日期格式的字符串，并返回要使用的日期对象.
• 如果字符串为空或格式不正确(在循环中进行比较)，则返回一个空的日期对象.

• parse string with any date format and return a date object to work with.
• return an empty date object, if the string is empty or malformed (for comparison in the loop).

要重现该错误，只需运行 myDate = CDate("24.01.2009")

To reproduce the error, simply run myDate = CDate("24.01.2009")

推荐答案

尝试使用Replace查看它是否对您有用.我认为上面提到过几次的问题是CDate函数在句点上令人窒息.您可以使用replace将其更改为斜杠.为了回答有关vba中可以解析任何日期格式的函数的问题，您没有一个非常有限的选择.

Try using Replace to see if it will work for you. The problem as I see it which has been mentioned a few times above is the CDate function is choking on the periods. You can use replace to change them to slashes. To answer your question about a Function in vba that can parse any date format, there is not any you have very limited options.

Dim current as Date, highest as Date, result() as Date 
For Each itemDate in DeliveryDateArray
    Dim tempDate As String
    itemDate = IIf(Trim(itemDate) = "", "0", itemDate) 'Added per OP's request.
    tempDate = Replace(itemDate, ".", "/")
    current = Format(CDate(tempDate),"dd/mm/yyyy")
    if current > highest then 
        highest = current 
    end if 
    ' some more operations an put dates into result array 
Next itemDate 
'After activating final sheet... 
Range("A1").Resize(UBound(result), 1).Value = Application.Transpose(result) 

这篇关于VBA将字符串转换为日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！