Excel Convert Dates to Fiscal Quarters and Years

Mynda Treacy

July 27, 2017

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

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Watch the Power Query Method Video

Subscribe YouTube

 

Watch the Formulas Method Video

Subscribe YouTube

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 1st; your fiscal quarters and years will be like so:

Excel Convert Dates to Fiscal Quarters and Years

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]

reference to cell A6

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.

CHOOSE will return 3

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 1st July to 30th 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:

IF function to convert dates

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:

fiscal periods for 4-5-4 calendars

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.

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

19 thoughts on “Excel Convert Dates to Fiscal Quarters and Years”

  1. Hi Myndy, I tried to use the choose formula to determine my fiscal quarter but i live in the UK and our Fiscal Year starts on 6th April so using the Month function with the choose function doesn’t work. Do you have a resolution or formula using my date of 6th April please

    Reply
    • Hi Mick,

      You need to use the same type of technique as the 4-5-4 calendar mentioned above. i.e. create a calendar lookup table.

      Mynda

      Reply
      • Hi Mynda,
        it took a while to get my head around it but managed eventually, it works great, Thanks!
        Am i correct in thinking that i would be be best to modify the same formula to derive my fiscal year also? because of the “in month” change?

        Reply
        • Hi Myndy, no need to respond, i did it anyway and it works a treat!! Thanks for the help and thanks for a great website, people like me really do need people like you, thanks again!! 🙂

          Mick Kitcher

          Reply
  2. Hi Mynda,
    Excellent collection of the different solutions to the date to quarter issue.Thanks!
    Cheers,
    Kevin Lehrbass

    Reply
  3. Hey Minda,
    Really cool to know the alternative method, I do the same using EDATE,CEILING and MONTH Functions

    =”Q”&CEILING(MONTH(EDATE(Table1[@Date],-6)),3)/3

    -6 can be adjusted with the fiscal year starting (in your case July)

    Reply
  4. Hi Mynda,

    Firstly just wanted to say that I love your newsletters. They have given me a lot of great tips!

    I looked into Fiscal Year formula’s for using with the Australian fiscal period (as you know beginning 1st July and ending 30th June) a little while ago now and I found the below to be choice. Hope you find it as nifty as I did.

    =YEAR(A1+184)

    What’s with the +184?
    That’s how many days are required to be added to the 1st July to get to the 1st January.

    Also I like to show the financial year period as it’s easier for me to read and I do this using a UDF to make it even easier to use in my workbooks.

    Public Function fYear(refDate As Double)
    fYear = Year(refDate + 184) – 1 & “-” & Year(refDate + 184)
    End Function

    -Fellow Sunshine Coast Excel User

    Reply
  5. I do something very similar but with
    =CHOOSE(MONTH([@Date])/3+0.7,4,1,2,3)
    and
    =YEAR([@Date])+(MONTH([@Date])>3)
    (we start in April)

    Reply

Leave a Comment

Current ye@r *