I have a column with dates in dd/mm/yyyy . I want to get rid of the dd.
although converting the date format does the job.. but the dd/mm/yyyy value
still remains on the formula bar. this then makes pivot table analysis
difficult as I want to analyse by month.

help please

To change the date format from dd/mm/yyyy to mm/yyyy and get rid of the dd:

1. Select the column with the dates you want to change.
2. Right-click on the selected cells and choose "Format Cells" from the context menu.
3. In the "Format Cells" dialog box, select the "Custom" category.
4. In the "Type" field, enter "mm/yyyy" (without the quotes).
5. Click "OK" to apply the new format to the selected cells.

To get rid of the original value in the formula bar:

1. Assuming your dates are in column A, insert a new column next to it (column B).
2. In cell B1, enter the formula "
   Formula:

   =TEXT(A1,"mm/yyyy") 

   " (without the quotes).
3. Copy the formula down to the rest of the cells in column B.
4. Select the entire column B and copy it.
5. Right-click on the first cell of column B and choose "Paste Special" from the context menu.
6. In the "Paste Special" dialog box, choose "Values" and click "OK".
7. Now you can delete column A, as you no longer need it.

This should give you a new column with only the month and year values, without the original dd/mm/yyyy value visible in the formula bar. You can use this new column for your pivot table analysis.

Add a column for the month =MONTH(A1) and copy down, and include that in the
pivot. Maybe add a year and day column as well.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"flow23" wrote in message
...
I have a column with dates in dd/mm/yyyy . I want to get rid of the dd.
although converting the date format does the job.. but the dd/mm/yyyy
value
still remains on the formula bar. this then makes pivot table analysis
difficult as I want to analyse by month.

help please

You could rightclick on the Date field (In the pivottable) and choose "Group and
show detail".

Then you could group my month (or month and year).

Another option might be to use another column of formulas:

=text(a2,"yyyy-mm")

And use that in the pivottable.



flow23 wrote:

I have a column with dates in dd/mm/yyyy . I want to get rid of the dd.
although converting the date format does the job.. but the dd/mm/yyyy value
still remains on the formula bar. this then makes pivot table analysis
difficult as I want to analyse by month.

help please

--

Dave Peterson