parsing a date and time field

[freeriderxlt]

I have a date and time field that I downloaded from a database. It
come across looking like the following:
"02/1/2009 14:37". I want to seperate the time from the date. When I
to to "Text to Columns" and parse the field it changes it to three
columns looking like this: "02/1/2009 0:00", "2:37 AM", "PM" I parsed
based on "Space delimiter". I am looking for suggestions on how to get
the correct time to display.

 

[Bernard Liengme]

I cannot reproduce this. I get the correct time
Tell us more

 

[Shane Devenshire]

Hi,

You are parsing as Delimited. I think you should use Fixed Width and then
put one line between the date and time.

 

[Pete_UK]

Assuming your date/times are in A1, then this will give you only the
date part:

=INT(A1)

and this will give you only the time part:

=MOD(A1,1)

Format the cells accordingly, then copy these down as required.

Hope this helps.

Pete

 

[freeriderxlt]

Assuming your date/times are in A1, then this will give you only the
date part:

=INT(A1)

and this will give you only the time part:

=MOD(A1,1)

Format the cells accordingly, then copy these down as required.

Hope this helps.

Pete



- Show quoted text -

The "=INT(a1) and =MOD(a1,1) solution worked, thank you.
The other solutions of using fixed width does not work because the
date and time fields vary and a straight line would cut off some
data.

Thanks for all the interest and suggestions.

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete