Need formulas to pull date and time from 1 cell to 2

[Dan B]

I have a report with date and time column. The data looks like this:
11/6/2008 2:18:00 PM

I need to pull the date into one cell and the time into another. How can I
do this when the date and time will vary in the number of characters? i.e
=left(a1,9) will miss part of the date if were another character long, like
11/16/2006.

Thanks

 

[T. Valko]

Try this:

A1 = 11/6/2008 2:18:00 PM

For the date:

=INT(A1)

Format as Date

For the time:

=MOD(A1,1)

Format as Time

 

[Michael]

From the main menu select:
Data -> Text To Columns
On the popup window select Delimited, then click next,then check mark the
Space box and click next
Click on Date and
From the Drop Down box on your right, select the format you prefer, click on
the next field (not the next button) and do the same for the time.
Then Finish

 

[Michael]

Sorry, you dont do anything for the Time field..

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.

 

[Dan B]

Perfect, thanks.

Try this:

A1 = 11/6/2008 2:18:00 PM

For the date:

=INT(A1)

Format as Date

For the time:

=MOD(A1,1)

Format as Time

 

[Dan B]

Thanks for your help.

Sorry, you dont do anything for the Time field..

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Perfect, thanks.