I get an exported file with the date and time column as follows:

6/12/2009 9:01:00 AM (all in one cell)

Is there a way to convert this to just 6/12/2009?

Also how would you convert to just the month?

Thanks!

There are two cases:

1. if the data is a genuine Excel date/time, then just format the cell to:
Custom mm/dd/yyyy

2. if the data is only a text string then use the formula:
=LEFT(A1,LEN(A1)-FIND(" ",A1)-2)

--
Gary''s Student - gsnu200902


"Nelson B." wrote:

I get an exported file with the date and time column as follows:

6/12/2009 9:01:00 AM (all in one cell)

Is there a way to convert this to just 6/12/2009?

Also how would you convert to just the month?

Thanks!

Insert three new columns to the right of this data
Select the column of values
Use Data | Text-to-Column; specify delimited by space; click Next
For the first field specify DMY or MDY as needed (not clear from your
example); click OK
Delete unrequited columns
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Nelson B." wrote in message
...
I get an exported file with the date and time column as follows:

6/12/2009 9:01:00 AM (all in one cell)

Is there a way to convert this to just 6/12/2009?

Also how would you convert to just the month?

Thanks!

Well€¦.
Considering the data is in single column and same format always with same
spacing€¦.

Process: Select the number of cells or data in couln and Go to Data in Menu
Bar--- Text to Columns---- Fixed width---- next--- check that the line
appears after the date and before the time---- Next ----- Date column should
be highlighted---- Go to Column data Format---- Select date and format(MDY or
DMY and so on) you wish to have------ now select the 2nd column besides
date--- go to column format--- select do not import column i.e. skip ------
and repeat the same for the 3rd column if any----- Now click finish---- you
should have what you want€¦€¦

If the dat is in not in same spacing than use delimited format instead of
Fixed width and click space for separator and uncheck everything else€¦. And
follow almost the same procdure€¦


Hope that is what you want€¦
If it helps€¦. Please click yes below€¦..


"Nelson B." wrote:

I get an exported file with the date and time column as follows:

6/12/2009 9:01:00 AM (all in one cell)

Is there a way to convert this to just 6/12/2009?

Also how would you convert to just the month?

Thanks!

Hi Nelson B.,

as far as 1st part of your questionis concerned, it is not clear whether u
are referencing the said cell elsewhere or not. If u r not using this cell
elsewhere then may be just formatting the cell as "dd/mm/yyyy" either in
date category or in general category will do the needful (eventhough it will
show the entire & actual contents of the cell in the formula bar, but only
date will be shownin the cell).

as regards second part of your question, u can also use the following
formula in the other column
=TEXT(celladdress,"mmmm")

click yes below, if it helps

"Nelson B." wrote:

I get an exported file with the date and time column as follows:

6/12/2009 9:01:00 AM (all in one cell)

Is there a way to convert this to just 6/12/2009?

Also how would you convert to just the month?

Thanks!

Wow that was easy! I hadn't even thought of changing the format of the
column! Thanks to all for your help.

"Gary''s Student" wrote:

There are two cases:

1. if the data is a genuine Excel date/time, then just format the cell to:
Custom mm/dd/yyyy

2. if the data is only a text string then use the formula:
=LEFT(A1,LEN(A1)-FIND(" ",A1)-2)

--
Gary''s Student - gsnu200902


"Nelson B." wrote:

I get an exported file with the date and time column as follows:

6/12/2009 9:01:00 AM (all in one cell)

Is there a way to convert this to just 6/12/2009?

Also how would you convert to just the month?

Thanks!