CODEWITHSUNDEEP
excel
user1898525
user1898525

Reputation: 133

Cannot convert date string to datevalue in Excel

I have some dates in a string in a column. Because the format seems to be M/DD/YYYY I sometimes get a VALUE error when using DATEVALUE. How can I convert a column to make sure all dates are correct. For example, the following 2 cells

9/29/2006 12:49:58.956 AM DATEVALUE gives an error
9/12/2008 5:36:59.356 PM DATEVALUE converts to 39791

Upvotes: 2

Views: 4229

Answers (3)

Fadi
Fadi

Reputation: 3322

it seems that your system settings use dd/mm/yyyy for short date format. and datevalue function uses this settings, so its try to read the day from the first part of the string and the month from the second part. as in your example 9/29/2006 12:49:58.956 AM there is no month with 29 then it gives error, and in the second example it gives 39791 = 9 december 2008 and NOT 12 september 2008.

If you change the short date format in your system settings to mm/dd/yyyythen datevalue function will work correctly, but this not acceptable, so we need to replace month with day and day with month to get correct date.

I don't know if we can do this with Excel formula but we can do that simply with VBA. So try this code:

Sub Test()
  Dim d1 As Variant, d2 As Variant, td As Date
  Dim Rng As Range, CL As Range

  Set Rng = Range("A1:A2") ' change this to your range

  For Each CL In Rng

     d1 = Split(CL.Value, " ")
     d2 = Split(d1(0), "/")
     td = DateSerial(d2(2), d2(0), d2(1))
     CL.Offset(0, 1) = td
     CL.Offset(0, 1).NumberFormat = "mm/dd/yyyy" ' change the Date format as you need
     'CL.Offset(0, 1).NumberFormat = "dd/mm/yyyy"

  Next
End Sub

Upvotes: 0

Elbert Villarreal
Elbert Villarreal

Reputation: 1716

You need to select the range (just one colunm) with the dates... Go to:

(in Excel 2010)

Data >>> Text to Columns

enter image description here Inside the dialog box select: 

Delimited >>> Next

enter image description here

Just select Tab checkbox.

enter image description here

And heres is the magic! You need to define the arrangement for your dates...

enter image description here

In the Date field: Choose the what you need (as you say in your question is M/D/Y)

Destination field: Make sure that is the same of the data you want to format.

And finish.

Upvotes: 1

David Manheim
David Manheim

Reputation: 2626

The problem you are having is probably that the data is getting interpreted as a general field, and shows up as, say 49:59.0 - which isn't text, which is what Datevalue() expects as input; for both inputs, Datevalue(CellID) words if you prepend the text with a ' - which ensures it is treated as text.

Upvotes: 0

Related Questions