Subtotal Dates By Month (or) Year
Excel Tip #029
Subtotal Dates By Month (or) Year
There may be times when you want to subtotal your data by month and year, however simply Subtotaling a column of dates won't work because that will create a subtotal for each day.
Subtotal Dates - Example
Here's a trick you can use to create subtotals for each month while ignoring the day...
1) Sorted your dates by selecting a single date within the table and from the Data tab click the sort A>Z button;
2) Next you need to apply a date/number format that displays the month but not the day (e.g. mmm yyyy). To do this, from the Number group on the Home tab, click the Number Format dropdown (or press CTRL+1) and choose More Number Formats..., then click Custom and enter mmm yyyy in the Type box. All of the dates now display the month and year (e.g. Apr 2015)
Formatting Dates
3) To subtotal your data based on the month and year in the Date column, from the Data tab, click Subtotal,. in the Subtotal dialog, 'At each change in' select your date column heading (i.e. Date), for 'Use function' select Sum, for 'Add subtotal to' use Amount. Subtotals will be inserted for each month/year instead of each day
Subtotal Dates
4) You can now format your dates back to the original date format to display the full dates (day, month and year) and the subtotaled rows will still show just the month and year
Dates in Original Format
There may be times when you want to subtotal your data by month and year, however simply Subtotaling a column of dates won't work because that will create a subtotal for each day.
Here's a trick you can use to create subtotals for each month while ignoring the day...
1) Sorted your dates by selecting a single date within the table and from the Data tab click the sort A>Z button;
2) Next you need to apply a date/number format that displays the month but not the day (e.g. mmm yyyy). To do this, from the Number group on the Home tab, click the Number Format dropdown (or press CTRL+1) and choose More Number Formats..., then click Custom and enter mmm yyyy in the Type box. All of the dates now display the month and year (e.g. Apr 2015)
3) To subtotal your data based on the month and year in the Date column, from the Data tab, click Subtotal,. in the Subtotal dialog, 'At each change in' select your date column heading (i.e. Date), for 'Use function' select Sum, for 'Add subtotal to' use Amount. Subtotals will be inserted for each month/year instead of each day
4) You can now format your dates back to the original date format to display the full dates (day, month and year) and the subtotaled rows will still show just the month and year
 |
| Subtotal Dates - Example |
Here's a trick you can use to create subtotals for each month while ignoring the day...
1) Sorted your dates by selecting a single date within the table and from the Data tab click the sort A>Z button;
2) Next you need to apply a date/number format that displays the month but not the day (e.g. mmm yyyy). To do this, from the Number group on the Home tab, click the Number Format dropdown (or press CTRL+1) and choose More Number Formats..., then click Custom and enter mmm yyyy in the Type box. All of the dates now display the month and year (e.g. Apr 2015)
 |
| Formatting Dates |
3) To subtotal your data based on the month and year in the Date column, from the Data tab, click Subtotal,. in the Subtotal dialog, 'At each change in' select your date column heading (i.e. Date), for 'Use function' select Sum, for 'Add subtotal to' use Amount. Subtotals will be inserted for each month/year instead of each day
|
| Subtotal Dates |
4) You can now format your dates back to the original date format to display the full dates (day, month and year) and the subtotaled rows will still show just the month and year
|
| Dates in Original Format |
Comments
Post a Comment