parsing a date and time field

  • Thread starter Thread starter freeriderxlt
  • Start date Start date
F

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.
 
Hi,

You are parsing as Delimited. I think you should use Fixed Width and then
put one line between the date and time.
 
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
 
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 -
Click to expand...

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.
 
Back
Top