Changing date to eliminate day in format mm/dd/yyyy
 

Assistance with removing day from date format. Wish to change column of
dates which are in format mm/dd/yyyy to new value yyyy-mm. Converted dates
in column are then to be used in a pivot table, to sort data by monthly
occurances. The pivot table presently sorts by daily occurances as the date
is in mm/dd/yyyy format.

Answer: Changing date to eliminate day in format mm/dd/yyyy
 

To remove the day from the date format in Excel:

1. Select the column of dates that 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 the following format:
   Formula:

   ---

   "yyyy-mm" 


   ---

5. Click "OK" to apply the new format to the selected cells.

To sort data by monthly occurrences using a pivot table:

1. Select the data range that you want to use in the pivot table.
2. Click on the "Insert" tab in the ribbon.
3. Click on "PivotTable" in the "Tables" group.
4. In the "Create PivotTable" dialog box, select the range of cells that you want to use for the pivot table.
5. Choose where you want to place the pivot table (e.g. a new worksheet or an existing one).
6. In the "PivotTable Fields" pane, drag the date column to the "Rows" area.
7. Drag any other columns that you want to include in the pivot table to the "Values" area.
8. Right-click on the date column in the pivot table and choose "Group" from the context menu.
9. In the "Grouping" dialog box, select "Months" and click "OK".
10. The pivot table will now be sorted by monthly occurrences based on the new date format.

Changing date to eliminate day in format mm/dd/yyyy
 

I wouldn't do it.

It sounds like you can group your data in the pivottable by month and year and
have exactly what you wanted.

But you could use a formula like:
=text(a1,"yyyy-mm")
to do the conversion.

(But try that grouping in the PT first. You'll like it.)

VA wrote:

Assistance with removing day from date format. Wish to change column of
dates which are in format mm/dd/yyyy to new value yyyy-mm. Converted dates
in column are then to be used in a pivot table, to sort data by monthly
occurances. The pivot table presently sorts by daily occurances as the date
is in mm/dd/yyyy format.

--

Dave Peterson