Splitting Column Data into 2 fields

[Guest]

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

 

[edessary]

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

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

 

[Guest]

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

 

[Biff]

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

 

[edessary]

If I understand you correctly try the following:

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