I need to be able to separate the Date and Time from thousands of entries
that are list as follows:

2/6/2005 2:36:45 PM

If the above is in the same cell, how can I get 2/6/2005 separated out into
its own cell?

Thank you.

One way:

=INT(A1)

format as a date if necessary.

In article ,
"JT" wrote:

I need to be able to separate the Date and Time from thousands of entries
that are list as follows:

2/6/2005 2:36:45 PM

If the above is in the same cell, how can I get 2/6/2005 separated out into
its own cell?

JT,

You can do this with INT(A2), formatted for the date format you want. But
there may be no need, if you're using Excel's date/time functions, or just
want to show only the date. Just format as desired. Format - Cells -
Number - Date, as desired. Or use Format - Cells - Number - Custom, and
roll your own date formats.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"JT" wrote in message
...
I need to be able to separate the Date and Time from thousands of entries
that are list as follows:

2/6/2005 2:36:45 PM

If the above is in the same cell, how can I get 2/6/2005 separated out
into
its own cell?

Thank you.

Perfect, thank you.

I know how to format the date and time differently, but I needed to get the
time portion out of the cell so I can use a pivot table to combine all the
similar dates together, not the date's and times, which makes them all
different due to the time stamp.

Thanks again.

"Earl Kiosterud" wrote:

JT,

You can do this with INT(A2), formatted for the date format you want. But
there may be no need, if you're using Excel's date/time functions, or just
want to show only the date. Just format as desired. Format - Cells -
Number - Date, as desired. Or use Format - Cells - Number - Custom, and
roll your own date formats.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"JT" wrote in message
...
I need to be able to separate the Date and Time from thousands of entries
that are list as follows:

2/6/2005 2:36:45 PM

If the above is in the same cell, how can I get 2/6/2005 separated out
into
its own cell?

Thank you.

I can't figure out how to post a new thread, so I'm going to reply to this
post as I'm having a similar issue.

I exported a bunch of data from an application into a CSV file. I then
opened the CSV file in Excel. The dates in this CSV file are in mm/dd/yyyy
hh:mm:ss AM/PM format. After I loaded the file in Excel I did the
Format-Cells-Date option and selected the mm/dd/yyyy option. In one column
the changes stuck in the other date columns, however, the dates are still
mm/dd/yyyy hh:mm:ss although they display as mm/dd/yyyy. When I try to
format the cells they show that they are as they should be.

I realize this thread is old, but I hope someone can help me. Please!

"Earl Kiosterud" wrote:

JT,

You can do this with INT(A2), formatted for the date format you want. But
there may be no need, if you're using Excel's date/time functions, or just
want to show only the date. Just format as desired. Format - Cells -
Number - Date, as desired. Or use Format - Cells - Number - Custom, and
roll your own date formats.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"JT" wrote in message
...
I need to be able to separate the Date and Time from thousands of entries
that are list as follows:

2/6/2005 2:36:45 PM

If the above is in the same cell, how can I get 2/6/2005 separated out
into
its own cell?

Thank you.

I realise that this thread is old but I have just come across a similar
problem.

Our own reporting system throws out time and date stamped data and when
pasted in to excel I was having dificulty converting 2007-10-18 10:18:58
into Oct 07. Suggest anyone with similar problems tries =TEXT(A2,"mmm yy")
extremely simple but hard to find.

If this does not work straight away convert the cell to columns and then
concatenate the date elements (ignoring the time elements), then try teh
above formula.

Hope this helps someone

"JT" wrote:

I need to be able to separate the Date and Time from thousands of entries
that are list as follows:

2/6/2005 2:36:45 PM

If the above is in the same cell, how can I get 2/6/2005 separated out into
its own cell?

Thank you.

Or just custom format A2 as mmm yy.

If you really want to separate the date you can also use

=INT(A2)






--


Regards,


Peo Sjoblom


"Rik385" wrote in message
...
I realise that this thread is old but I have just come across a similar
problem.

Our own reporting system throws out time and date stamped data and when
pasted in to excel I was having dificulty converting 2007-10-18 10:18:58
into Oct 07. Suggest anyone with similar problems tries =TEXT(A2,"mmm yy")
extremely simple but hard to find.

If this does not work straight away convert the cell to columns and then
concatenate the date elements (ignoring the time elements), then try teh
above formula.

Hope this helps someone

"JT" wrote:

I need to be able to separate the Date and Time from thousands of entries
that are list as follows:

2/6/2005 2:36:45 PM

If the above is in the same cell, how can I get 2/6/2005 separated out
into
its own cell?

Thank you.

Peo

Unfortunately on Office 2007 and the data that was presented the obvious mmm
yy didn't work nor did =INT(A2) - curious I know, but nothing is ever simple,
but it keeps us on our toes.

Rik

"Peo Sjoblom" wrote:

Or just custom format A2 as mmm yy.

If you really want to separate the date you can also use

=INT(A2)






--


Regards,


Peo Sjoblom


"Rik385" wrote in message
...
I realise that this thread is old but I have just come across a similar
problem.

Our own reporting system throws out time and date stamped data and when
pasted in to excel I was having dificulty converting 2007-10-18 10:18:58
into Oct 07. Suggest anyone with similar problems tries =TEXT(A2,"mmm yy")
extremely simple but hard to find.

If this does not work straight away convert the cell to columns and then
concatenate the date elements (ignoring the time elements), then try teh
above formula.

Hope this helps someone

"JT" wrote:

I need to be able to separate the Date and Time from thousands of entries
that are list as follows:

2/6/2005 2:36:45 PM

If the above is in the same cell, how can I get 2/6/2005 separated out
into
its own cell?

Thank you.

That is probably because the dates were not numerical, if you try

=ISTEXT(A2)

will you get TRUE?


--


Regards,


Peo Sjoblom


"Rik385" wrote in message
...
Peo

Unfortunately on Office 2007 and the data that was presented the obvious
mmm
yy didn't work nor did =INT(A2) - curious I know, but nothing is ever
simple,
but it keeps us on our toes.

Rik

"Peo Sjoblom" wrote:

Or just custom format A2 as mmm yy.

If you really want to separate the date you can also use

=INT(A2)






--


Regards,


Peo Sjoblom


"Rik385" wrote in message
...
I realise that this thread is old but I have just come across a similar
problem.

Our own reporting system throws out time and date stamped data and when
pasted in to excel I was having dificulty converting 2007-10-18
10:18:58
into Oct 07. Suggest anyone with similar problems tries =TEXT(A2,"mmm
yy")
extremely simple but hard to find.

If this does not work straight away convert the cell to columns and
then
concatenate the date elements (ignoring the time elements), then try
teh
above formula.

Hope this helps someone

"JT" wrote:

I need to be able to separate the Date and Time from thousands of
entries
that are list as follows:

2/6/2005 2:36:45 PM

If the above is in the same cell, how can I get 2/6/2005 separated out
into
its own cell?

Thank you.

So it's obviously not a date in Excel's eyes, so you'll need to convert the
text into a date..
--
David Biddulph

"Rik385" wrote in message
...
Peo

Unfortunately on Office 2007 and the data that was presented the obvious
mmm
yy didn't work nor did =INT(A2) - curious I know, but nothing is ever
simple,
but it keeps us on our toes.

Rik

"Peo Sjoblom" wrote:

Or just custom format A2 as mmm yy.

If you really want to separate the date you can also use

=INT(A2)






--


Regards,


Peo Sjoblom


"Rik385" wrote in message
...
I realise that this thread is old but I have just come across a similar
problem.

Our own reporting system throws out time and date stamped data and when
pasted in to excel I was having dificulty converting 2007-10-18
10:18:58
into Oct 07. Suggest anyone with similar problems tries =TEXT(A2,"mmm
yy")
extremely simple but hard to find.

If this does not work straight away convert the cell to columns and
then
concatenate the date elements (ignoring the time elements), then try
teh
above formula.

Hope this helps someone

"JT" wrote:

I need to be able to separate the Date and Time from thousands of
entries
that are list as follows:

2/6/2005 2:36:45 PM

If the above is in the same cell, how can I get 2/6/2005 separated out
into
its own cell?

Thank you.