Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
VA VA is offline
external usenet poster
 
Posts: 1
Default 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.
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Format date dd.mm.yyyy to dd/mm/yyyy Kiwi User Excel Discussion (Misc queries) 7 May 7th 23 11:44 AM
change date format from dd/mm/yyyy to mm/yyyy flow23 Excel Discussion (Misc queries) 3 April 4th 23 11:26 AM
Date format yyyy/mm/dd MUSD Tech Excel Discussion (Misc queries) 3 May 8th 08 02:43 AM
Date format from yyyy-mm-dd-hh Ron Coderre Excel Discussion (Misc queries) 12 December 30th 07 04:33 AM
how do I change date from mm/dd/yyyy to dd:mm:yyyy format in Excel Jack Wilson New Users to Excel 4 July 18th 06 01:57 PM


All times are GMT +1. The time now is 03:56 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"