Convert String or Text to Date

**biznez** · 10-19-2016, 02:08 PM

Hi,
I'm trying to convert 161017 to date 10/17/16 using VBA. I thought Cdate function would work but no luck. Thanks for help.

ValueDate = Cdate(Format(Worksheets("Data").Range("A1").Value, "YY/MM/DD"))

**Marc L** · 10-19-2016, 02:21 PM

Hi,

first try ValueDate = CDate(Range("Data!A1").Value) …

If it fails, so the cell text does not respect your regional date format, just see CDate VBA inner help !
In this case, you can split text to form a valid date for CDate or DateSerial functions using Mid function (to see in VBA help) …

**MarvinP** · 10-19-2016, 02:24 PM

Hi biznet,
Try

Function ToDate(Num6 As Double) As Date
    Dim NumStr As String
    NumStr = CStr(Num6)
    ToDate = DateValue(Mid(NumStr, 3, 2) & "/" & Left(NumStr, 2) & "/" & Right(NumStr, 2))
End Function

See attached using this function above.
UDF to change YYMMDD number ToDate.xlsm

**biznez** · 10-19-2016, 02:35 PM

Thanks Marvin and Marc. Both of your solutions worked

Convert String or Text to Date

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Convert String or Text to Date

Re: Convert String or Text to Date

Re: Convert String or Text to Date

Re: Convert String or Text to Date

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Convert a text string to a date.

[SOLVED] Convert text string into date

[SOLVED] Convert text string to date

[SOLVED] Convert a Text String Date to Date Serial Number

Convert text string to a date?

[SOLVED] Convert text string to date

How do I convert a text string into a date?

Bookmarks

Bookmarks

Posting Permissions