Search code examples
exceldateformulafinance

How to get Quarters for financial year based on date

I'm trying to display quarters based on the financial year: Q1 starting in April, Q2 from July, etc.

In column B there is a list of dates, and in column A I want the corresponding quarter & year. There are 2 parts I can't figure out:

  • currently for 01 JAN 2018 it shows Q4 2018. Although the date is 2018, it's the final quarter of 2017 and should show Q4 2017

  • how to get the display format like Q1 17/18 (eg)

At the moment I have:

=CHOOSE(INT((MONTH(B1)-1)/3)+1,"Q4","Q1","Q2","Q3") & " " & YEAR(B1)

Is it possible without any helper columns?

Solution

  • Subtract a Boolean expression from the Year part:

    YEAR(B1) - (MONTH(B1)<4)

    If the MONTH is Less than 4 it will subtract 1 from the year, otherwise it will subtract 0:

    =CHOOSE(INT((MONTH(B1)-1)/3)+1,"Q4","Q1","Q2","Q3") & " " & YEAR(B1) - (MONTH(B1)<4)