Calculating a fiscal quarter from a date

Question

I'm using the following formula to find the fiscal quarter (where the year starts in April) from a date in a list:

="Q"&INT((MONTH([Date of inspection])-1)/3)

HOWEVER -instead of displaying Q4 for dates in Jan/Feb/Mar the formula returns Q0.

Can anyone advise me how to amend my formula to avoid this?

Answer 1

This Formula is better to read:

="Q" & CHOOSE( MONTH([Date of inspection])  ,1,1,1 ,2,2,2 ,3,3,3 ,4,4,4)

More: www.viewmaster365.com/functions

Answer 2

Using a combination of the techniques provided by others I was able to return the values for both our company's fiscal quarter and fiscal year, which in our case starts in July. First section uses choose to map the month to the corresponding FY Quarter, and second section checks to see if the month is before or after July to get it into the right Fiscal year as well.

A date of 7/5/2019 ends up coming out as "Q1 FY20"

=IF(ISBLANK([Date]),"",(CONCATENATE("Q",(CHOOSE(MONTH([Date]),3,3,3,4,4,4,1,1,1,2,2,2))," FY",(IF(MONTH([Date])>=7,RIGHT(YEAR([Date])+1,2),RIGHT(YEAR([Date]),2))))))

Original poster's question would use the following for April:

=IF(ISBLANK([Date]),"",(CONCATENATE("Q",(CHOOSE(MONTH([Date]),4,4,4,1,1,1,2,2,2,3,3,3))," FY",(IF(MONTH([Date])>=4,RIGHT(YEAR([Date])+1,2),RIGHT(YEAR([Date]),2))))))

Answer 3

Try wrapping it in an IF statement. This should do the trick:

=IF("Q"&INT((MONTH([Date of inspection])-1)/3)=0, 1, ="Q"&INT((MONTH([Date of inspection])-1)/3))

Answer 4

This will work =IF(ISBLANK([column]),"",(CONCATENATE("Q"&(INT((MONTH([Column])-1)/3)+1)&" "&(YEAR([column])))))

Answer 5

Since you are using the INT function, dividing by three, and subtracting one, the first three months of the year WILL return 0. The CHOOSE function can work very well for this, but the trick is getting the list of values in the correct order, as the function will choose the value from the list in order based on the number of the month. January = 1, February = 2, March = 3, etc. So, it will choose the 1st value for January, the 2nd value for February, and so on. Since your fiscal year starts in April, January, February, and March are the fourth quarter; therefore, your first three values should be 4, followed by three 1s, three 2s, and three 3s, like this:

="Q" & CHOOSE(MONTH([Date of Inspection]),4,4,4,1,1,1,2,2,2,3,3,3)

January, February, and March will return Q4; April, May, and June will return Q1; July, August, and September will return Q2; and October, November, and December will return Q3.

I got around checking for a null value by making it a required column, and don't have to use an IF statement.

Answer 6

Here's my simplistic code to write "Q M YYYY"

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": {
"operator": "+",
"operands": [
"Q ",
"=if(getMonth(Date(@currentField)) == 1, ' 1' , '')",
"=if(getMonth(Date(@currentField)) == 2, ' 1' , '')",
"=if(getMonth(Date(@currentField)) == 3, ' 1' , '')",
"=if(getMonth(Date(@currentField)) == 4, ' 2' , '')",
"=if(getMonth(Date(@currentField)) == 5, ' 2' , '')",
"=if(getMonth(Date(@currentField)) == 6, ' 2' , '')",
"=if(getMonth(Date(@currentField)) == 7, ' 3' , '')",
"=if(getMonth(Date(@currentField)) == 8, ' 3' , '')",
"=if(getMonth(Date(@currentField)) == 9, ' 3' , '')",
"=if(getMonth(Date(@currentField)) == 10, ' 4' , '')",
"=if(getMonth(Date(@currentField)) == 11, ' 4' , '')",
"=if(getMonth(Date(@currentField)) == 12, ' 4' , '')",
" ",
"=getYear(Date(@currentField))"

]

}
}