Formula to calculate fiscal quarter

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!

Best Answer

Answers

  • L_123
    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

  • 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

  • THANK YOU SO MUCH!!!

  • 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

  • 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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!