Solved How do I convert a complex date to a simple date in Excel?!?

May 31, 2012 at 12:36:58
Specs: Windows 7
I received a .csv file from a company with a date field that looks like this:
Tue Jan 03 12:12:20 PST 2012

I 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!!!


See More: How do I convert a complex date to a simple date in Excel?!?

Report •


#1
May 31, 2012 at 17:20:13
✔ Best Answer
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

http://www.skeptic.com/


Report •

#2
June 1, 2012 at 13:19:29
Thank You!!!!

Report •

Related Solutions


Ask Question