Calculate Yearly Quarter

Question

Trying to create a column that will determine the financial quarter that a date field currently represents.

The 1st quarter should always begin on November 1st.

Current formula:

=IF(ISBLANK([Actual Date]),"",(CONCATENATE("Q"&(INT((MONTH([Actual Date])-1)/3)+1)&" "&(YEAR([Actual Date])))))

((MONTH([Actual Date])-1)/3)+1)

How would I amend this to make Q1 start on November 1st?

As always, any help is greatly appreciated.

Answer 1

You could try this instead, to calculate accidents I need to use a reporting year which runs from 01/11/2017 - 31/10/2018. Happily, we both have the same reporting year/financial year.

We need to create some calculated fields and some 'single line of text' fields.

YEAR

To split off the year data, create a 'single line of text' field with the formula below:

=""&YEAR([Accident Date])

• Instead of 'Accident Date' you would use your field called 'Actual Date'.

MONTH

To split off the month data, create a 'single line of text' field with the formula below:

=TEXT([Accident Date],"mm")

Again you use your 'Actual Date' field instead of my [Accident Date].

PERIOD

Next make a new 'single line of text' field called PERIOD. You would call this field Quarter instead of Period. Formula:

=IF(Month<"11",Year,TEXT(Year+1,"0"))

What is this doing? It looks at the Month field, if Month is 11 or more, it grabs the Year value and does +1 that calculates my reporting year. Otherwise if Month is less than 11 add +0 to the Year field, so keep it the same.

For your solution (??)

You'd need to modify the Period formula to make a Quarter calculation: this is the important bit, but I don't know how to do it. I believe it can be done in a single formula, with the 'quarter query' nestled within the brackets. The syntax is tricky though...

So begin with the Period formula and adapt...sorry I can't help with the final part, but I hope this answer sends you in the right direction...