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):
And if you want to prefix it with ‘Q’, then use this formula:
Convert dates to years with this formula:
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.
Excel Formulas Method
Note: These are .xlsx file please ensure your browser doesn't change the file extension on download.
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 1st; 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
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:
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:
And finally, ‘Q’ is joined to the number returned by CHOOSE using the ampersand, so we get:
Of course, if you don’t want the number prefixed by ‘Q’, you can just use this formula:
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:
Looking at the formula more closely:
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:
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:
- The dates in the Qtr Start column must be sorted in ascending order.
- 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
- Choose Function
- CHOOSE Function Toggle Trick
- Using VLOOKUP on a sorted list
- SWITCH Function – new in Excel 2016 Office 365, it can be used in place of some IF/VLOOKUP/CHOOSE functions
Thanks to Mike Alexander for the CHOOSE trick for fiscal quarters.
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
You need to use the same type of technique as the 4-5-4 calendar mentioned above. i.e. create a calendar lookup table.
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?
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!! 🙂
Glad I could help, Mick 🙂
Really helped me with the fiscal quarter calculator – since our FY begins in Oct.
Great to hear, Subhra!
Is there a YouTube in the fiscal year tutorial?
No, but I’ll add it to the list 🙂
Excellent collection of the different solutions to the date to quarter issue.Thanks!
Thanks, Kevin! Some great ones in the comments too.
Really cool to know the alternative method, I do the same using EDATE,CEILING and MONTH Functions
-6 can be adjusted with the fiscal year starting (in your case July)
Thanks, Chandeep. Lots of options for quarters.
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.
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)
-Fellow Sunshine Coast Excel User
Love the ‘Year’ trick, Jayden! Nice.
I do something very similar but with
(we start in April)
Nice, I like those options too.