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