Converting Dates to Quarters for Usual or Custom Fiscal Year using Excel Power Query

Converting Dates to Quarters for Usual or Custom Fiscal Year using Excel Power Query

A while back we learnt how to convert normal to date to yearly quarters using formulas and in that we learnt different presentations.

Same can be achieved with even lesser efforts using power query. Power query has inbuilt time intelligence capability to convert dates to quarters.

There are number of ways to do it. Lets start with the easiest one.

Just the number of Quarter

Step 1: Download the practice workbook and you will find the data formatted as table in source worksheet. Have an active cell inside the table and to go Data tab > In get and transform group click “From Table” button. Power query window will open with the table imported.

Step 2: Right click on the Date column header and select Duplicate Column. It will insert the copy as the right most column.

Step 3: Rename the column to Quarters by double clicking the header and hitting Enter key after inputting name. Right click the column header again and select Transform > Quarter > Quarter. It will convert the dates to quarter numbers.

Yes! Quarter numbers without writing a single line of code and even lifting your hands from the mouse.

Quarter numbers with “Q” and year appended

To get this job done, unfortunately we can’t use the duplicated column we inserted in the method above. It is better to use custom column.

Step 1: Having power query window active go to add column tab > Click Custom Column button. A sub-window will open where you can write a formula.

Step 2: Change the name of column from Custom to Quarter. And paste the following formula in the input bar:

=”Q”&Text.From(Date.QuarterOfYear([Date]))&”-“&Text.From(Date.Year([Date]))

Above formula is basically a concatenation of four text elements:

1. “Q”
2. Quarter number which is calculated using Date.QuarterOfYear formula after applying it on particular date which is fetched from [Date] column and converted to text for concatenation purposes.
3. “-” just to add a sign between quarter number and year
4. Year number which is calculated in the same manner like quarter number in point 2 with a difference in formula that we used Date.Year

Click OK and it will insert a new custom column with date formatted as “QX-XXXX”

So now that we are done with the easy part, lets make things a little difficult for ourselves because, life isn’t easy. Say we have custom fiscal year that doesn’t start from January. In this case, help from power query’s inbuilt time intelligence is of little help and we will have to improvise.

Finding Quarter numbers with custom fiscal year

By default or should I say generally fiscal year is considered starting from January and ends at December. For any fiscal year started other than January is considered technically custom fiscal year in Excel world.

For many countries for different purposes, fiscal year is usually July till June like in Pakistan we have Tax Fiscal Year with July as first month.

So if we have custom fiscal year, default quarter numbers won’t help and we have calculate our own. This can be done using power query functions or M language.

There are many ways to do this but I will explain the one I find easiest and firstly explain it with helper column and later make it concise to avoid helper column.

For our example of custom fiscal year I am assuming fiscal starts from July.

With Helper Column

We need helper column to adjust default month number we extract from dates. Adjustment is needed as we divide the month number by three to get quarter number and later round it up.

Step 1: Go to add column tab > click custom column. Name the column “AMonthNum” and put the following formula in the input box:

= if Date.Month([Date])>6 then Date.Month([Date])-6 else Date.Month([Date])+6

As we are assuming our fiscal starts July we are deducting 6 from months with number greater than 6 and adding 6 to the remaining. If your fiscal year is starting from April then formula will be following:

if Date.Month([Date])>3 then Date.Month([Date])-3 else Date.Month([Date])+9

Back to our scenario with first month as July, we will have month numbers adjusted and now July will give 1, August will have 2 and so on.

Step 2: Go to add column tab > click custom column. Name the column “AQNum” and put the following formula in the input box:

=Number.RoundUp([AMonthNum]/3)

Step 3: If requirement is just quarter numbers then you only need above two steps. If you also require correct fiscal year appended to expression then add a custom column with the name “AFY” having following formula:

= if Date.Month([Date])>6 then Date.Year([Date])+1 else Date.Year([Date])

Again I am considering Pakistan Tax Year as example that works as follows:

1. Tax year 2016: 1 July 2015 – 30 June 2016
2. Tax year 2017: 1 July 2016 – 30 June 2017
3. Tax year 2018: 1 July 2017 – 30 June 2018
4. Tax year 2019: 1 July 2018 – 30 June 2019

So you got the idea. Above formula helps us achieve it.

Step 4: To complete the expression we know simply have to concatenate the contents of AMonthNum, AQNum and AFY results with the following formula in a new custom column:

= “Q”&Text.From([AQNum])&”-“&Text.From([AFY])

Without Helper Column

In the above approach we needed three helper columns for:

1. Adjusted month number
2. Adjusted quarter number
3. Adjusted fiscal year number

Helper columns are used to avoid complex and lengthy formulas and scripts. Now that we have grasped the concept we can move on and look at a single formula that can achieve it without helper columns.

Go to add column tab > click custom column, name it the way you deem fit and put the following formula:

"Q"&Text.From(Number.RoundUp((

if Date.Month([Date])>6

then Date.Month([Date])-6

else Date.Month([Date])+6)/3))

&"-"&Text.From(

if Date.Month([Date])>6

then Date.Year([Date])+1

else Date.Year([Date]))

Above is basically combination of all the formulas applied in each helper column. This avoids the need of helper columns to be used.