2

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?

6 Answers 6

3

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

1
  • You corrected the Formula, Arden, but the Original Poster wanted the first 3 months of the year to be Q4. That is why it was 4,4,4 , 1,1,1 , 2,2,2 , 3,3,3 Jun 13, 2017 at 20:14
1

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))))))
0

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))
0

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

0

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.

0

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))"

]

}
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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