Date Format

[goodmachine]

I took a date that was in text format like so [TABLE="width: 181"]
<tbody>[TR]
[TD="class: xl63, width: 181"]Thu May 23 13:29:43 PDT 2013[/TD]
[/TR]
</tbody>[/TABLE]

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
Thu	May	PDT

<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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"]23[/TD]
[TD="align: right"]13:29:43[/TD]

[TD="align: right"]2013[/TD]
[TD="align: right"]5/23/2013[/TD]

</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:

[TABLE="width: 930"]
<tbody>[TR]
[TD="class: xl63, width: 186"]May[/TD]
[TD="class: xl63, width: 186"]28[/TD]
[TD="class: xl64, width: 186"]14:30:57[/TD]
[TD="class: xl63, width: 186"]PDT[/TD]
[TD="class: xl63, width: 186"]2013
[/TD]
[/TR]
</tbody>[/TABLE]


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:

[TABLE="width: 930"]
<tbody>[TR]
[TD="class: xl63, width: 186"]May[/TD]
[TD="class: xl63, width: 186"]28[/TD]
[TD="class: xl64, width: 186"]14:30:57[/TD]
[TD="class: xl63, width: 186"]PDT[/TD]
[TD="class: xl63, width: 186"]2013
[/TD]
[/TR]
</tbody>[/TABLE]


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:

[TABLE="width: 930"]
<tbody>[TR]
[TD="class: xl63, width: 186"]May[/TD]
[TD="class: xl63, width: 186"]28[/TD]
[TD="class: xl64, width: 186"]14:30:57[/TD]
[TD="class: xl63, width: 186"]PDT[/TD]
[TD="class: xl63, width: 186"]2013[/TD]
[/TR]
</tbody>[/TABLE]


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:

[TABLE="width: 930"]
<tbody>[TR]
[TD="class: xl63, width: 186"]May[/TD]
[TD="class: xl63, width: 186"]28[/TD]
[TD="class: xl64, width: 186"]14:30:57[/TD]
[TD="class: xl63, width: 186"]PDT[/TD]
[TD="class: xl63, width: 186"]2013[/TD]
[/TR]
</tbody>[/TABLE]


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.