# Calculate A Quarter Using Fiscal Year

• 1
• Question
• Updated 3 years ago
I am using a date field for completion of a project. I would like a calculated field to establish the right quarter. I did manage to find a solution but that does not necessarily work for my situation.  We Have A FY from Jul to June. And for a quarter we use the last Sunday in the third month of the quarter.  Would someone have a solution for this?

Following is formula I am hoping to amend which was created by Joe Y in QB Support. Thank you Joe.

If(Month([Date])>=1 and Month([Date])<=3, "Q1 " & Year([Date]),
Month([Date])>=4 and Month([Date])<=6, "Q2 " & Year([Date]),
Month([Date])>=7 and Month([Date])<=9, "Q3 " & Year([Date]),
Month([Date])>=10 and Month([Date])<=12, "Q4 " & Year([Date]))
• 3,152 Points Posted 3 years ago

• 1
• 20 Points

Does your FY start on July 1st or the last Monday of June?

-Jeff
• 3,152 Points Thanks Jeff.  The FY starts on the first Monday in end June. For example, this year our FY starts on 27 June.  meaning that last Sunday is the end of the month.
• 20 Points

Give this formula a try. Let me know how it works for you.

Case(true,
//Q1
[Date]>=FirstDayOfWeek(FirstDayOfMonth(ToDate("7/1/"&Year([Date]))))+Days(1) and

//Q2
[Date]>=FirstDayOfWeek(FirstDayOfMonth(ToDate("10/1/"&Year([Date]))))+Days(1) and

//Q3
[Date]>=FirstDayOfWeek(LastDayOfMonth(ToDate("12/1/"&Year([Date]))))+Days(1)) and
[Date]<=FirstDayOfWeek(LastDayOfMonth(ToDate("3/1/"&Year([Date])))), "Q3 "&Year([Date]),

//Q4
Month([Date])<=6 and
[Date]>=FirstDayOfWeek(FirstDayOfMonth(ToDate("4/1/"&Year([Date]))))+Days(1) and
[Date]<=FirstDayOfWeek(LastDayOfMonth(ToDate("7/1/"&Year([Date])))),"Q4 "&Year([Date]))

-Jeff
• 3,152 Points Jeff

This is a great solution.  It works great.  Thanks so much for your help.
• 20 Points

-Jeff
• 3,152 Points Jeff you were kind enough to provide me the solutions.  It is great in terms of a field but problem is that when I am using the Qtr field in a chart it is sorting on the first two letters.  As a result, my sort order appears as under:

Qtr1 2017
Qtr 2 2017
Qtr 2 2018
Qtr 3 2017
Qtr 3 2018

So it is sorting on the Qtr only.

I was hoping to make these appear so that these also get sorted on year so that all quarters appear as under"

Qtr1 2017
Qtr 2 2017
Qtr 3 2018
Qtr 4 2018

If I cannot get what I want, can I please amend the formula so that I could get just the quarters for one FY?   I tried amending the formula but did not have much luck. Or if I could sort the field using another field to make it appear as such.  If nothing else, could I have a text field "FY'  concatenated instead of the year although this is still not the solution for my chart..