Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

excel - Convert text date format to date format

I have a date as text in this format - "22nd July 2016". How do I convert it into date format using Excel-VBA.

Thanks in advance.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

One way is to just strip-off the ordinal indicator:

Public Function dateOR(s As String) As Date
    ary = Split(s, " ")
    ary(0) = Left(ary(0), Len(ary(0)) - 2)
    dateOR = DateValue(Join(ary, " "))
End Function

enter image description here

and without VBA you can use the worksheet formula:

=DATEVALUE(SUBSTITUTE(A1,MID(A1,FIND(" ",A1)-2,2),""))

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...