Splitting Column Data into 2 fields

Hi All,

I have a column that recieves date and time data in the following format:
01/03/2006 16:00:00 (mm/dd/yyyy hh:mm:ss)

It is causing me some troubles, namely in calculating elapsed time between 2
data points because Excel doesn't seem to like the format the data comes in
and recognizes it as text instead of a date.

Is there a way to split this into 2 columns, one for date the other for
time, (Sort of a reverse concatate?) and is this my best course of action or
is there a more efficient solution to this?

Thanks kindly!
Kevin

You could convert the text to a value using the text function as
follows:

=TEXT(A2,"mm/dd/yyyy hh:mm:ss")

Thanks Edessary, is it possible to make this conditional, I assume through an
IF statement, so that if the reference cell is not Null, the Text is
converted to a value? I ask because the Imported Date/Time stamp comming
over as a Text field is of variable length, and I'd like to make the
converted column expand or collapse with the number of reference cells
imported into the column.

I'm not sure that made sense or not...

Thanks!
Kevin

"[email protected]" wrote:

> You could convert the text to a value using the text function as
> follows:
>
> =TEXT(A2,"mm/dd/yyyy hh:mm:ss")
>
>

Hi!

Try this:

Try it on a single cell first to see if it will work!

Select one of the faux date cells
Goto Data>Text to Columns>Next>Next
Select Date and MDY format from the drop down
Finish

Biff

"Kevin" <[email protected]> wrote in message
news:[email protected]...
> Hi All,
>
> I have a column that recieves date and time data in the following format:
> 01/03/2006 16:00:00 (mm/dd/yyyy hh:mm:ss)
>
> It is causing me some troubles, namely in calculating elapsed time between
> 2
> data points because Excel doesn't seem to like the format the data comes
> in
> and recognizes it as text instead of a date.
>
> Is there a way to split this into 2 columns, one for date the other for
> time, (Sort of a reverse concatate?) and is this my best course of action
> or
> is there a more efficient solution to this?
>
> Thanks kindly!
> Kevin

If I understand you correctly try the following:

=IF(A2="","",TEXT(A2,"mm/dd/yyyy hh:mm:ss"))