Formula to calculate fiscal quarter

Lee Ratzlaff

Hello!

I am trying to create an IF statement to show the federal fiscal quarter an event occurred. Could someone please help troubleshoot my syntax?

Federal Quarters:

Q1: Oct, Nov, Dec (10, 11, 12)

Q2: 1, 2, 3

Q3: 4, 5, 6

Q4: 7, 8, 9

Not working syntax

=IF(MONTH([6m NOMs Date]@row) = >10, 1, IF(MONTH([6m NOMs Date]@row) < 3, 2, IF(MONTH([6m NOMs Date]@row) = >7, 4, IF(MONTH([6m NOMs Date]@row) = >4, 3))))

Thanks!

L_123

=IF(MONTH([6m NOMs Date]@row) >9, 1, IF(MONTH([6m NOMs Date]@row) < 4, 2, IF(MONTH([6m NOMs Date]@row) < 7, 4, IF(MONTH([6m NOMs Date]@row) <10, 3))))

I missed one of the equals signs. Yours has the same issue. The #unparseable is due to there being a space between the = and the 7

L_123

=IF(MONTH([6m NOMs Date]@row) >9, 1, IF(MONTH([6m NOMs Date]@row) < 4, 2, IF(MONTH([6m NOMs Date]@row) = < 7, 4, IF(MONTH([6m NOMs Date]@row) <10, 3))))

Give that a try

Lee Ratzlaff

That threw an #invalid operation error.

I created a month hidden field that pull just the number of the month, so I can work with that instead

I tried

=if([6m Month]@row) > 9, 1, if([6m Month]@row) < 4, 2, if([6m Month]@row) = < 7, 4, if([6m Month]@row) < 10, 3)))) but that threw #unparseable

thanks

L_123

=IF(MONTH([6m NOMs Date]@row) >9, 1, IF(MONTH([6m NOMs Date]@row) < 4, 2, IF(MONTH([6m NOMs Date]@row) < 7, 4, IF(MONTH([6m NOMs Date]@row) <10, 3))))

I missed one of the equals signs. Yours has the same issue. The #unparseable is due to there being a space between the = and the 7

Lee Ratzlaff

THANK YOU SO MUCH!!!

Paul Pindell

I needed something similar. Our fiscal year also starts in October, so a date of 2/2/20 would be Q2. I also wanted to concatenate the FY with the quarter. So that the output for the date of 2/2/20 would be Q2FY20. I only wanted the last 2 digits of the year so I also incorporated a RIGHT(YEAR(Date@ row),2)

I ran into the issue where a date of 10/2/19 needed to render as Q1FY20, so I had to add an if statement for months >=10 to add one to the output so I ended up using the INT(YEAR(Date@row) + 1) formula to add 1 to the integer of the year.

Here is my final working formula: If not evident, the Date is stored in a column named "Date"

="Q" + IF(MONTH(Date@row) >= 10, "1FY", IF(MONTH(Date@row) >= 7, "4FY", IF(MONTH(Date@row) >= 4, "3FY", "2FY"))) + IF(MONTH(Date@row) >= 10, RIGHT(INT(YEAR(Date@row) + 1), 2), RIGHT(YEAR(Date@row), 2))

Hope this helps

Paul

Colleen Jones

I'm going to try Paul's formula, because I also need fiscal year with quarter. My fiscal year starts in February, so I'll have to adjust the numbers a bit and see if I can get it to work. Thanks Paul!