Split combined date time data
 

From file dump have combined date time cells eg "14/04/03 14:20" (value
37725.59722). Want to perform time analysis so need to split to 2 separate
cells; eg. "14/04/2003" (value 37725) & "14:20" (value 0.59722). Is there a
fnc to do this? (Currently convert cell to value, then fncs trunc & cell
less int(cell) then refmt to date dd/mm/yy & time hh:mm respectively)

One way:

Data/Text to Columns. Select Delimited, and click Next. Check the Space
checkbox and click Finish.


In article ,
"Mark Ada" wrote:

From file dump have combined date time cells eg "14/04/03 14:20" (value
37725.59722). Want to perform time analysis so need to split to 2 separate
cells; eg. "14/04/2003" (value 37725) & "14:20" (value 0.59722). Is there a
fnc to do this? (Currently convert cell to value, then fncs trunc & cell
less int(cell) then refmt to date dd/mm/yy & time hh:mm respectively)

Hi JE,

That's not working for me. Not working means nothing
happens.

What does work is T to C.
Fixed width
Place the delimiter before the decimal point.

Still have to manually format as desired.

Couldn't come up with a formula solution that's really
much better than what the OP is already doing.

Biff

-----Original Message-----
One way:

Data/Text to Columns. Select Delimited, and click Next.
Check the Space
checkbox and click Finish.


In article <D84AFF1F-CA34-456B-AA06-
,
"Mark Ada" wrote:

From file dump have combined date time cells
eg "14/04/03 14:20" (value
37725.59722). Want to perform time analysis so need to
split to 2 separate
cells; eg. "14/04/2003" (value 37725) & "14:20" (value
0.59722). Is there a
fnc to do this? (Currently convert cell to value, then
fncs trunc & cell
less int(cell) then refmt to date dd/mm/yy & time hh:mm
respectively)
.

Hmm...I don't see a decimal point in the OP's "14/04/03 14:20".

What version of XL are you using. My solution works fine in XL03 and
XL04.

If the date is not in your date format (i.e., if it's dmy and you use
mdy), to to the third pane of the Text to Columnyou can select first
column in the third pane and choose the dmy option from the Date
dropdown. That will coerce what the parser would see as a text value to
a date.


In article ,
"Biff" wrote:

That's not working for me. Not working means nothing
happens.

What does work is T to C.
Fixed width
Place the delimiter before the decimal point.

Still have to manually format as desired.

Couldn't come up with a formula solution that's really
much better than what the OP is already doing.

DOH!

My mistake. I thought the OP wanted to separate the
numeric value and convert to date/time format.

Biff

-----Original Message-----
Hmm...I don't see a decimal point in the OP's "14/04/03
14:20".

What version of XL are you using. My solution works fine
in XL03 and
XL04.

If the date is not in your date format (i.e., if it's dmy
and you use
mdy), to to the third pane of the Text to Columnyou can
select first
column in the third pane and choose the dmy option from
the Date
dropdown. That will coerce what the parser would see as a
text value to
a date.


In article ,
"Biff" wrote:

That's not working for me. Not working means nothing
happens.

What does work is T to C.
Fixed width
Place the delimiter before the decimal point.

Still have to manually format as desired.

Couldn't come up with a formula solution that's really
much better than what the OP is already doing.
.