I have imported a text file where all fields were character and came in as
general. They look like:
Nov 17 2009 9:39AM
I want to compute time differences between columns, but I can't get them
converted to Date/Time format.
Thanks very much for any ideas.

This worked for me with data that looked like that one sample you shared.

I selected the offending column (one column at a time)
Edit|replace
what: AM (two characters)
with: _AM (space character, AM (3 characters))
replace all

Then I made sure that there were two empty columns to the right of this
offending columns

Select the original column
Data|text to columns
Fixed width
But I only had a line between the date and year.
I chose Date (mdy) for the date
and plopped the output into those adjacent cells.

With the offending data in column A and the parsed data in columns B:C,
I put:
=b1+c1
in column D (another helper column!)
and dragged down
Then I formatted column D in an unambiguous date/time format:
mmm dd, yyyy hh:mm:ss
to verify that all my data was converted successfully.

Then I converted to values (edit|copy, edit|paste special|values) and deleted
the original column and the two intermediate helper columns.



Odd sent date when open attachment OWA wrote:

I have imported a text file where all fields were character and came in as
general. They look like:
Nov 17 2009 9:39AM
I want to compute time differences between columns, but I can't get them
converted to Date/Time format.
Thanks very much for any ideas.

--

Dave Peterson

Ps. I may have had to do this twice:

I selected the offending column (one column at a time)
Edit|replace
what: AM (two characters)
with: _AM (space character, AM (3 characters))
replace all

and for the PM's:

I selected the offending column (one column at a time)
Edit|replace
what: PM (two characters)
with: _PM (space character, PM (3 characters))
replace all



Odd sent date when open attachment OWA wrote:

I have imported a text file where all fields were character and came in as
general. They look like:
Nov 17 2009 9:39AM
I want to compute time differences between columns, but I can't get them
converted to Date/Time format.
Thanks very much for any ideas.

--

Dave Peterson

The problem is the lack of a comma sepearting day from year, and the AM/PM
being connected to the number string. Doing some text manipulation, and using
the assumption that all dates are 2000-2009:

=VALUE(LEFT(A2,FIND(200,A2)-2)&", "&MID(A2,FIND(200,A2),4)&"
"&TRIM(MID(A2,FIND(":",A2)-2,5))&" "&RIGHT(A2,2))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Odd sent date when open attachment OWA" wrote:

I have imported a text file where all fields were character and came in as
general. They look like:
Nov 17 2009 9:39AM
I want to compute time differences between columns, but I can't get them
converted to Date/Time format.
Thanks very much for any ideas.

I'm not getting this to work right. Do I have to enter this formula cell by
cell? I tried it in on one cell and it seems to give the cell the data from
the cell below it.
Thank you very much for your help with this.

"Luke M" wrote:

The problem is the lack of a comma sepearting day from year, and the AM/PM
being connected to the number string. Doing some text manipulation, and using
the assumption that all dates are 2000-2009:

=VALUE(LEFT(A2,FIND(200,A2)-2)&", "&MID(A2,FIND(200,A2),4)&"
"&TRIM(MID(A2,FIND(":",A2)-2,5))&" "&RIGHT(A2,2))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Odd sent date when open attachment OWA" wrote:

I have imported a text file where all fields were character and came in as
general. They look like:
Nov 17 2009 9:39AM
I want to compute time differences between columns, but I can't get them
converted to Date/Time format.
Thanks very much for any ideas.

Luke assumes your "dates" are in column A starting at A2.

Enter the fomula in B2 and double-click on fill handle of B2 to copy down
with A2 incrementing to A3, A4 etc.

If "dates" are somewhere else, use a helper column and change A2 to the
actual starting cell.


Gord Dibben MS Excel MVP

On Wed, 2 Dec 2009 11:01:01 -0800, Odd sent date when open attachment OWA
osoft.com wrote:

I'm not getting this to work right. Do I have to enter this formula cell by
cell? I tried it in on one cell and it seems to give the cell the data from
the cell below it.
Thank you very much for your help with this.

"Luke M" wrote:

The problem is the lack of a comma sepearting day from year, and the AM/PM
being connected to the number string. Doing some text manipulation, and using
the assumption that all dates are 2000-2009:

=VALUE(LEFT(A2,FIND(200,A2)-2)&", "&MID(A2,FIND(200,A2),4)&"
"&TRIM(MID(A2,FIND(":",A2)-2,5))&" "&RIGHT(A2,2))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Odd sent date when open attachment OWA" wrote:

I have imported a text file where all fields were character and came in as
general. They look like:
Nov 17 2009 9:39AM
I want to compute time differences between columns, but I can't get them
converted to Date/Time format.
Thanks very much for any ideas.