I would like to change the underlying date format from e.g. 01-May-09 to
May-09. I can change the format so that the displayed date is in correct
format but the underlying date is still in DD/MM/YYYY format. The purpose is
to do pivot charts to show how many defects per month over the last five
years.
--
Doug M

Hi Doug,

To convert the date format from DD/MM/YYYY to MMYY format, you can use the

Code:

TEXT

function in Excel. Here are the steps:

1. Select the column that contains the dates you want to convert.
2. Right-click on the selected cells and choose "Format Cells".
3. In the "Number" tab, choose "Custom" from the list on the left.
4. In the "Type" field, enter "mmm-yy" (without the quotes) and click "OK".
5. The dates in the selected column will now be displayed in the format "May-09".
6. To convert the underlying date values to this format, you can use the
   Code:

   TEXT

   function. In a new column, enter the formula
   Code:

   =TEXT(A1,"mmm-yy")

   where A1 is the cell containing the original date value.
7. Copy the formula down to all the cells in the new column.
8. The new column will now contain the dates in the format "May-09" as text values. You can use these values to create pivot charts and analyze the data by month.

Rather than reformat the cell to Custom mmm-yy

use something like:

=TEXT(A1,"mmm-yy")

--
Gary''s Student - gsnu200909


"Doug Muegge" wrote:

I would like to change the underlying date format from e.g. 01-May-09 to
May-09. I can change the format so that the displayed date is in correct
format but the underlying date is still in DD/MM/YYYY format. The purpose is
to do pivot charts to show how many defects per month over the last five
years.
--
Doug M

This solution worked perfectly - Thank you - saved me considerable time!!!
--
Doug M


"Doug Muegge" wrote:

I would like to change the underlying date format from e.g. 01-May-09 to
May-09. I can change the format so that the displayed date is in correct
format but the underlying date is still in DD/MM/YYYY format. The purpose is
to do pivot charts to show how many defects per month over the last five
years.
--
Doug M