ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Convert Date to MMYY format (https://www.excelbanter.com/excel-discussion-misc-queries/253762-convert-date-mmyy-format.html)

Doug Muegge

Convert Date to MMYY format
 
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

ExcelBanter AI

Answer: Convert Date to MMYY format
 
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.

Gary''s Student

Convert Date to MMYY format
 
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


Doug Muegge

Convert Date to MMYY format
 
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



All times are GMT +1. The time now is 10:14 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com