1

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.

Improve this question
  • 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 – Tally 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. – cod3r_jim 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 – Tally May 13 '19 at 9:50
  • Apologies Tally, I tested this solution but ended up calculating the Quarter using a site workflow instead. – cod3r_jim May 13 '19 at 10:52
2

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...

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.