Date Format

[goodmachine]

I took a date that was in text format like so

Thu May 23 13:29:43 PDT 2013

<tbody>
</tbody>

and I used the Text to Columns function. I then concatenated the month, day, and year, and then added a slash, so the end result of the new data appears as May/23/2013.

Unfortunately, Excel does not recognize this format. How can I modify my new date so that Excel recognizes it so I can sort by the earliest date (the years vary).

Thanks,
goodmachine

 

[Jonmo1]

Try this on the original data (before text to columns)
=(REPLACE(LEFT(A1,FIND(" ",A1,5)+2),1,4,"")&", "&RIGHT(A1,4))+0

 

[shg]

Duplicate post: http://www.mrexcel.com/forum/excel-questions/705320-date-conversion.html#post3482858

 

[JoeMo]

Another option after you have used the text-to-columns to separate the original data. Format cell G1 as a date.
Sheet9

*	A	B	C	D	E	F	G
1	Thu	May	23	13:29:43	PDT	2013	5/23/2013

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:30px;"><col style="width:33px;"><col style="width:21px;"><col style="width:57px;"><col style="width:31px;"><col style="width:35px;"><col style="width:68px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula G1 =DATEVALUE(B1&" "&C1&", "&F1) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[goodmachine]

Hi JoeMo,

I tried your method, but I'm getting an error message. After using the Text to Columns function I get the following:

May

28

14:30:57

PDT

2013

<tbody>
</tbody>

When I use your formula, and then apply it to my cells, I get the following:


=DATEVALUE(D4&" "&E4&","&H4)

I appreciate the help.

Thanks,

goodmachine

 

[goodmachine]

Hi JoeMo,

I tried your method, but I'm getting an error message. After using the Text to Columns function I get the following:

May

28

14:30:57

PDT

2013

<tbody>
</tbody>

When I use your formula, and then apply it to my cells, I get a #VALUE error with the following formula:


=DATEVALUE(D4&" "&E4&","&H4)

I appreciate the help.

Thanks,

goodmachine

 

[goodmachine]

Hi JoeMo,

I tried your method, but I'm getting an error message. After using the Text to Columns function I get the following:

May

28

14:30:57

PDT

2013

<tbody>
</tbody>

When I use your formula, and then apply it to my cells, I get a #VALUE error with the following formula:


=DATEVALUE(D4&" "&E4&","&H4)

I appreciate the help.

Thanks,

goodmachine

 

[goodmachine]

Thanks, I will try this also.

 

[JoeMo]

Hi JoeMo,

I tried your method, but I'm getting an error message. After using the Text to Columns function I get the following:

May

28

14:30:57

PDT

2013

<tbody>
</tbody>

When I use your formula, and then apply it to my cells, I get a #VALUE error with the following formula:


=DATEVALUE(D4&" "&E4&","&H4)

I appreciate the help.

Thanks,

goodmachine

Maybe you could tell me which of the cells you show are D4, E4 and H4? without that information I can only guess you are not entering the correct cells in the formula.