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

domdc1

Board Regular
Joined
Apr 10, 2006
Messages
61
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
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

domdc1

Board Regular
Joined
Apr 10, 2006
Messages
61
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

Well-known Member
Joined
Mar 26, 2008
Messages
1,490
Can you upload a sample of the data somewhere please?

Thanks
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows
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

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.
Code:
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,550
Members
414,316
Latest member
ExcelLee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top