Converting a 'date' in .csv file to an actual excel 'date'

[domdc1]

Hi there all. I exported a file in a .CSV format, I have saved it as an excel file but im unable to convert the date field to a real date. I have numerous cells with the following data: Jan-07-2014 04:11:22 PM, Jan-13-2014 03:27:07 PM, Jan-14-2014 01:37:40 PM etc. I need to convert these cells to a date. In the end I need to count the number of days between 2 ranges of dates but i dont know how to do this unless they are in a date format.

I searched old posts & found =datevalue(a1) but it did not work. Any suggestions would be greatly appreciated.
thanks

 

[BrianMH]

With a formula try =datevalue(trim(a1))

There may be extra whitespace characters that trim won't work on though.

I've created a macro for just this sort of thing. You can find it on the below post.

http://www.mrexcel.com/forum/excel-questions/748573-remove-space-start-cells.html#post3678127

 

[domdc1]

Hi Brian. thanks for the suggestion. It did not work. When trying to convert the cell to a date it still shows as #VALUE!

 

[BrianMH]

Can you upload a sample of the data somewhere please?

Thanks

 

[Snakehips]

domdc1,

Are you ok to use vba?

If so then maybe try one of these options.

A User Defined Function - AltF11 - Insert Module and copy in this code

Function MyDate(Mdt As String)
MyDate = Int(CDate(Mdt))
End Function

Then use in a cell eg =MyDate(A2)


Or copy the below to a code module or to the module of the sheet in question.

Sub DomDate()
'Assuming active sheet
'Assuming text dates A2: A?? to become dates in B2:B??
Application.Screenupdating = False
Lastr = Cells(Rows.Count, "A").End(xlUp).Row
For Each cell In Range("A1:A" & Lastr)
cell.Offset(0, 1) = Int(CDate(cell))
Next cell

Application.Screenupdating = True
End Sub

You may need to edit the columns to suit your data.
I have included Int (integer) to effectively resolve the time element to 0
It can be removed if time is required.


Hope that helps.