# Calculate Yearly Quarter

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.

• This post looks like it might be useful for you: sharepoint.stackexchange.com/questions/173588/… There are other similar posts, try searching under the [calculated-column] or [calculated-column-formula] tags Dec 11 '17 at 12:14
• That post is what I've based my formula on. I had already searched for previous questions of a similar fashion but there are not many relating to financial quarter calculations. Dec 11 '17 at 12:49
• Hi Jim, did this work for you in the end? If so could you mark as Answered please? ta, Tally May 13 '19 at 9:50
• Apologies Tally, I tested this solution but ended up calculating the Quarter using a site workflow instead. May 13 '19 at 10:52

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.