• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Convert Dates to Fiscal Quarters and Years

You are here: Home / Excel Formulas / Excel Convert Dates to Fiscal Quarters and Years
July 27, 2017 by Mynda Treacy

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.
Please enter a valid email address.

Power Query Method.
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

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.

More Excel Formulas Posts

ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.


Category: Excel Formulas
Previous Post:Power Query Unpivot Scenarios
Next Post:VBA IF THEN ELSEif then else

Reader Interactions

Comments

  1. Mick Kitcher

    October 11, 2020 at 9:06 pm

    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
    • Mynda Treacy

      October 12, 2020 at 8:37 am

      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
      • Mick Kitcher

        October 12, 2020 at 7:25 pm

        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
        • Mick Kitcher

          October 12, 2020 at 7:41 pm

          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
          • Mynda Treacy

            October 12, 2020 at 8:00 pm

            Glad I could help, Mick 🙂

  2. Subhra

    September 17, 2020 at 3:54 am

    Really helped me with the fiscal quarter calculator – since our FY begins in Oct.

    Reply
    • Mynda Treacy

      September 17, 2020 at 8:51 am

      Great to hear, Subhra!

      Reply
  3. Greg

    July 8, 2020 at 5:42 am

    Is there a YouTube in the fiscal year tutorial?

    Reply
    • Mynda Treacy

      July 8, 2020 at 8:42 am

      No, but I’ll add it to the list 🙂

      Reply
  4. Kevin Lehrbass

    July 29, 2017 at 12:04 am

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

    Reply
    • Mynda Treacy

      July 29, 2017 at 9:39 am

      Thanks, Kevin! Some great ones in the comments too.

      Reply
  5. Chandeep

    July 28, 2017 at 4:51 pm

    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
    • Mynda Treacy

      July 28, 2017 at 5:43 pm

      Thanks, Chandeep. Lots of options for quarters.

      Reply
  6. Jayden Bradow

    July 28, 2017 at 3:38 pm

    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
    • Mynda Treacy

      July 28, 2017 at 3:59 pm

      Love the ‘Year’ trick, Jayden! Nice.

      Reply
  7. jim

    July 27, 2017 at 8:55 pm

    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
    • Mynda Treacy

      July 27, 2017 at 10:29 pm

      Nice, I like those options too.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.