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

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

--
Biff
Microsoft Excel MVP


"Dan B" wrote in message
...
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

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
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Dan B" wrote:

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

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

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

Michael Arch.




"Michael" wrote:

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
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Dan B" wrote:

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

Perfect, thanks.


"T. Valko" wrote in message
...
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

--
Biff
Microsoft Excel MVP


"Dan B" wrote in message
...
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

Thanks for your help.


"Michael" wrote in message
...
Sorry, you dont do anything for the Time field..

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

Michael Arch.




"Michael" wrote:

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
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Dan B" wrote:

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

You're welcome!

--
Biff
Microsoft Excel MVP


"Dan B" wrote in message
...
Perfect, thanks.


"T. Valko" wrote in message
...
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

--
Biff
Microsoft Excel MVP


"Dan B" wrote in message
...
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