Converting dates to different formats

[Nelson B.]

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!

 

[NBVC]

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!

It is a matter of formatting the celll../

Go to Format|Cells and from the Number tab, select Date and then choos
your preferred format...

To display month only, choose Custom and then enter something like

m for month number
mmm for month abbrev.
mmmm for full month nam

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com

 

[Gary''s Student]

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)

 

[Bernard Liengme]

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

 

[YESHWANT]

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.]

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

 

[p45cal]

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!

This depends on whether the date in the cell has become an Excel dat
proper.
To check, select just *one *of the cells and go into the dropdown menu
(xl2003) and choose *Format|Cells...*. On the *Number *tab in th
*Category *box, select *General*. Just to the right of this you shopul
see Sample and something beneath that. Is that a number such a
40153.37569, or pretty much the same as what you saw in the cell?
Come out of the box with the *Cancel *button.

If you saw a number then it's a proper Excel date, if not then som
converting needs to be done.

I await your answer before continuing.