I received a .csv file from a company with a date field that looks like this:

Tue Jan 03 12:12:20 PST 2012I have tried using the Data Type options to change this to a mm/dd/yyyy format but it will not apply. I tried converting it to a serial number using*mm/dd/yyyy and it will not work ~ nothing happens. I tried using a DATEVALUE formula but keep getting a #VALUE error .. I obviously don't know how to lay it out. Can anyone give me the DATEVALUE formula for this example? I am not sure what "right", "left" or "mid" represent or how to count the value placements.

THANKS!!!

Try this: With your date in cell A1,

First, format cell B1 as a DATE field

Right click your mouse, select Format Cell, Select Number Tab,

select Date, choose the date format you want.Next, in cell B1 enter the formula:

=DATEVALUE(MID(A1,5,3)&" "&MID(A1,9,2)&", "&RIGHT(A1,4))

That should give you what your looking for.

MIKE

Thank You!!!!

Ask Your Question

Weekly Poll