Solved Change Excel text format to date

July 15, 2011 at 18:51:19
Specs: Macintosh 10.6.8, 3.0.6 GHz/8 GB
In Excel for Mac 2011, I have a column formatted General with information like this: 5/31/2011 7:02 PM PDT. How can I remove the time, then change the date to the format yyyy/mm/dd, so I can sort by date?

(Maybe all I have to do in order to sort by date is remove the time? If so, how?)

Thank you,

David


See More: Change Excel text format to date

Report •


#1
July 15, 2011 at 19:41:23
✔ Best Answer
Try this:

With your data in cell A1

First, Custom Format cell B1 as yyyy/mm/dd

Next, in B1 enter the formula:

=DATEVALUE(LEFT(A1,FIND(" ",A1,1)))

MIKE

http://www.skeptic.com/


Report •

#2
July 15, 2011 at 19:59:59
Thank you for your reply--I appreciate your help.

I had tried that (or something similar) before, but it didn't work.

Just before signing in and seeing your message, I was finally able to answer my own question. The problem was the time-zone indicator (PST, PDT). Once I removed that, the column could be sorted by date regardless of the d/m/y format.

David


Report •

#3
July 16, 2011 at 07:15:09
I had tried that (or something similar) before, but it didn't work.

If you had tried my formula you would have seen that all it does it cut the beginning of the string, starting on the left side, up to the space character.
It then takes that string, which in your example is 5/31/2011 and converts it to a real date with the format of yyyy/mm/dd.

The string PDT at the end would have no affect on the formula.

You did not specify you wanted to retain the time.

But, I'm Glad you solved you problem, and shared your solution.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
July 16, 2011 at 11:29:31
Thank you for your follow-up. I apologize for having glossed over your solution, which does work as you said.

The other ones I had tried were from <http://www.techonthenet.com/excel/questions/remove_time.php>. I couldn't get them to work before but now see that they also work if I first remove the time-zone indicators. (Yours removes them by itself.)

Thank you again for your help.

David


Report •

Ask Question