Excel Convert Dates to Fiscal Quarters and Years

If your company’s fiscal year runs in line with the calendar year then it’s easy to convert dates into quarters and years for reporting.

Convert dates to quarters with this formula (where cell A2 contains your date):

=ROUNDUP(MONTH(A2)/3,0)

And if you want to prefix it with ‘Q’, then use this formula:

="Q"&ROUNDUP(MONTH(A2)/3,0)

Convert dates to years with this formula:

=Year(A2)

Or you might like to skip that and just let a PivotTable automatically group the dates for you.

However, if your company has a fiscal year starting in July, like most of us in Australia do, or any other month, then we can either use Power Query or wrangle some formulas to convert dates to fiscal quarters and years.

Download the Workbooks

Watch the Power Query Method Video

 

Watch the Formulas Method Video

Excel Formula to Convert Dates to Fiscal Quarters

If your fiscal period starts in line with the beginning of a month then you can use a clever CHOOSE function trick to calculate the fiscal quarter from the date.

Let’s say your fiscal year starts on July 1^st; your fiscal quarters and years will be like so:

The CHOOSE Function will be the star of this formula. The syntax is fairly simple:

=CHOOSE(index_num, value1, value2, value3…..up to 254 values)

For example, the formula below

=CHOOSE(3,"Cat","Dog","Horse")

Returns:

=Horse

Because the index number argument is 3, and Horse is the third value in the list. See, easy. Let’s step it up a notch.

The table below contains dates in column A and in column B I’ve used a CHOOSE formula to convert the date into the fiscal quarters.

Note: My date format is dd/mm/yyyy and my data is in an Excel table, so the reference to cell A6 is the Structured Reference; [@Date]

Let’s look at the formula more closely:

="Q"&CHOOSE(MONTH([@Date]),3,3,3,4,4,4,1,1,1,2,2,2)

The MONTH Function returns the month number from the ‘Date’ column. E.g. MONTH(1/2/2017) will return the month number 2, for February. CHOOSE then uses this result as its first argument; the index_num.

We can see below that CHOOSE will return ‘3’ because it’s the second value in the list of values.

So our formula now evaluates to:

="Q"&3

And finally, ‘Q’ is joined to the number returned by CHOOSE using the ampersand, so we get:

=Q3

Of course, if you don’t want the number prefixed by ‘Q’, you can just use this formula:

=CHOOSE(MONTH([@Date]),3,3,3,4,4,4,1,1,1,2,2,2)

You can modify the CHOOSE values to suit different fiscal year start dates, for example, but not limited to:

• Feb 1 =CHOOSE(MONTH([@Date]),4,1,1,1,2,2,2,3,3,3,4,4)
• Apr 1 =CHOOSE(MONTH([@Date]),4,4,4,1,1,1,2,2,2,3,3,3)
• Oct 1 =CHOOSE(MONTH([@Date]),2,2,2,3,3,3,4,4,4,1,1,1)
• And so on…

Excel formula to Convert Dates to Fiscal Years

Once you’ve got your fiscal quarters, the next challenge is to get your fiscal years. Remember, my fiscal year is from 1^st July to 30^th June, so we just need to determine if the month is in the first 6 months of the year, or the second 6 months.

We can use an IF Function like so:

Looking at the formula more closely:

=IF(MONTH([@Date])<7,YEAR([@Date]),YEAR([@Date])+1)

In English it reads:

If the Month number in the Date column is less than 7, then the fiscal year is the Year from the Date column, otherwise it’s the Year from the Date column + 1, since the second half of the year falls into the following year’s fiscal period.

If your fiscal year starts in September then you can modify the IF formula like so:

=IF(MONTH([@Date])<9,YEAR([@Date]),YEAR([@Date])+1)

And so on for other fiscal year starting months.

Fiscal Periods for 4-5-4 Calendars

If you work for a retailer then you’re probably familiar with the 4-5-4 calendar format. The layout of the calendar in a 4 week, 5 week, 4 week pattern allows like for like sales comparisons by lining up holidays, and ensures the same number of Saturdays and Sundays in comparable months.

If your company uses a 4-5-4 calendar then you can use a VLOOKUP formula that references a table with your period start dates sorted in ascending order, and their corresponding quarter and year:

The are 2 key points to this method:

1. The dates in the Qtr Start column must be sorted in ascending order.
2. The last argument for the VLOOKUP formula is 1 or TRUE: ="Q"&VLOOKUP([@Date],FiscalPeriods,3,1)

Tip: the Qtr End column isn’t required, but I’ve included it for completeness.

More Related Tutorials

1. Choose Function
2. CHOOSE Function Toggle Trick
3. Using VLOOKUP on a sorted list
4. SWITCH Function – new in Excel 2016 Office 365, it can be used in place of some IF/VLOOKUP/CHOOSE functions

Thanks

Thanks to Mike Alexander for the CHOOSE trick for fiscal quarters.