How to Calculate the Calendar Year Quarter for a Date in Excel

Formulas to Calculate a Calendar Year Quarter

Formulas to Calculate a Calendar Year Quarter

After watching my previous video tutorial, “How to Place Dates in a Fiscal Year Quarter,” several viewers requested that I demonstrate how to calculate the calendar year quarter for a date.

This requires “nesting” the MONTH() /3 Function inside either the ROUNDUP() or the CEILING() Function. I break these formulas down step-by-step in this video so that you can see how they work.

Formatting Numbers to Include Text

As a bonus, I demonstrate how to format the formula result cells to include the text “Quarter.” This is really simple to create as a custom format.

Download this Video Podcast from iTunes

You can watch and download this – and many of my – video tutorials for free at iTunes. Here is the link to my iTunes video podcast, “Danny Rocks Tips and Timesavers.”

Watch Video in High Definition

Follow this link to watch this Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Visit my Secure Online Shopping Website

I invite you to visit my online shopping site – http://shop.thecompanyrocks.com – to preview and purchase my training resources. I guarantee your satisfaction 100%. I will refund your purchase if, for any reason, you are not satisfied with my products!

 

 

How to Take Advantage of Report Filters in Excel Pivot Tables

Starting with Excel 2007, Pivot Tables now include a “drop field” labeled “Report Filters.” The Report Filters area replaces the area labeled the “Pages” drop area in Excel 2003 and earlier. Report Filters is a more descriptive and intuitive name. So , this is a welcome change.

How to Organize Pivot Table

Use Report Filters in Pivot Table

In this lesson, I am responding to an additional request from one of my viewers who wants me to demonstrate how the “Report Filters” drop zone will provide incredible reporting power in a Pivot Table.

Request from a Viewer

In this lesson, my viewer is a “hydrologist” who analyzes average Water Flows by day and by month in a “Water Year.” If you use a “Fiscal Year” reporting structure, you will want to pay attention to this lesson. In this case, the “Water Year” begins on October 1 and ends on September 30. My viewer needs my help in separating out individual Pivot Table Reports organized by Water Year – placed in the Report Filters Drop Zone.The Pivot Tables show the average daily water flow by date. He told me that he is concrned that that Pivot Table Report may not be able to “account for Leap Years.”

Not to worry!

Watch this Excel Video Tutorial, as I demonstrate how to answer my viewer’s query and also give you some “solid tips” to get the results that you need when using Excel!

Watch this Excel Video Tutorial in HD Mode on my YouTube Channel

Here is the link to my YouTube video channel for this video.

Learn how to “Quickly Create Pivot Table Reports!”

Create a Pivot Table for a Water or Hydrological Year

A viewer asked for my help in creating a Pivot Table Report that is based on a Water Year – also known as a Hydrological Year. In North America, a Water Year begins on October 1 and ends on September 30. If you use a Fiscal Year in your reporting, you will want to watch this Excel Training Video Lesson.

Pivot Table for Water Year

Pivot Table for Water Year

Add Two Fields to Source Data

In order for a Pivot Table to summarize non-calendar years and quarters you will need to have two additional fields in your source data:

  1. A Fiscal Year (Water Year) Field – Use the IF(), MONTH() and YEAR() Functions to produce this.
  2. A Fiscal Quarter (Hydrological) Quarter Field – Use the CHOOSE() Function to produce this.

Now you are ready to create your Pivot Table Report.

Group Dates by Month and Year

Begin by grouping the Date Field by Month and Year – not, in this case, by Quarter. Now, remove the new Year field that you just created and add in the Water Year Field and the Hydrological Quarter to the Pivot Table. Continue to arrange the Pivot Table to suit your needs.

Since there is no direct way to change a Calender Year into a Fiscal Year or Water Year, you must prepare your data prior to beginning to create your Pivot Table. Now that you have watched this video lesson, you know how to do this!

Learn how to “Quickly Create Excel Pivot Tables and Charts”

Related Excel Video Lessons

Follow this link to watch this video in High Definition mode on my YouTube Channel – DannyRocksExcels