• Products
    • Sage Intelligence Reporting
      • Enrolment
    • Velixo
      • Enrolment
        • Velixo for Acumatica
        • Velixo for Sage Intacct
  • Services
    • Consulting
    • Training
    • Development
  • Support
    • Support Overview
    • Create Support Ticket
    • Book A Session
    • Knowledgebase
  • About
    • About Us
  • Contact
  • Products
    • Sage Intelligence Reporting
      • Enrolment
    • Velixo
      • Enrolment
        • Velixo for Acumatica
        • Velixo for Sage Intacct
  • Services
    • Consulting
    • Training
    • Development
  • Support
    • Support Overview
    • Create Support Ticket
    • Book A Session
    • Knowledgebase
  • About
    • About Us
  • Contact
Quick Support
Book Demo

How to Subtotal Dates by Month and Year in Excel

By admin on July 21, 2017April 19, 2019

If your data has a column of dates, you may want to subtotal dates by month and year. However, when using the Subtotal command, the desired result is not being achieved as it creates a subtotal for each day.

You are welcome to download the workbook to practice.
Applies To: Microsoft® Excel® for Windows 2010, 2013, and 2016.
If your data is in a table format, you will first need to convert the table to a normal range of data before following the below steps.  To convert your data, from the Table Tools > Design menu, select Tools > Convert to Range.  This will remove all table functionality from the data except the table formatting.

To subtotal by month and year following these simple steps:
Select a cell within your date range and from the Data tab select the sort A>Z button to sort your dates in the correct order.
Apply a date format to display your date range as Month and Year. Select your date range and on the Home Tab, select More Number Formats from the General drop down list in the Number section. Once the Format Cells dialogue box is open, select Custom and either enter mmm-yy in the Type box or scroll down to select the right format.
            
The dates will now be converted and displayed as the month and year only.
Select a cell in the date section, then select the Data tab and click.

Once the Subtotal Dialogue Box is open, select Date from the drop-down list under the At each change in:, select Sum from the drop-down list under Use function: and select the relevant fields under the Add subtotal to: section.

Subtotals are now inserted for each month instead of each day.
Finally, format your dates back to their original format to display the full date again. The subtotals are still only displayed for each month.

As you can see, it is easy to analyze the data when the subtotals are arranged by months and not dates. In that way, you can quickly see the total for each month leading to effective decision making.
The post How to Subtotal Dates by Month and Year in Excel appeared first on Sage Intelligence.

Powered by WPeMatico

Post navigation

Avoiding the Ref# error: How to convert formulas to values in Excel
8 tips to use when working with reports in the Excel Report Designer for Sage Intelligence

Recent Posts

  • Why Working In Excel Works
  • Alchemex Welcomes Velixo 6 For MYOB Advanced
  • Welcome to Velixo Reports Version 4.2
  • Part 3 of 3: Learn how to create a Management Pack in Sage 50cloud Intelligence
  • Part 3 of 3: Create and customize reports easily with the Financial Report Designer

Search

Contact Us

Phone: +61 2 9890 2339
Email: sales@alchemex.com.au
Address: PO Box 212, Deaken West, Canberra, Australia, 2600

Products

  • Intelligence Reporting
  • Velixo Reports

Services

  • Consulting
  • Training
  • Development

About

  • About Us
  • Partners
  • Blog

Support

  • New Ticket
  • Community (Coming Soon)
  • Knowledgebase

Phone: +61 2 9890 2339
Sales: sales@alchemex.com.au
Support: support@alchemex.com.au

Facebook-f Linkedin

© All rights reserved Alchemex Pty Ltd 2020

Made with ❤ by Unfazed Ltd