Fiscal Quarter and year return on formula

Options

Hello,

I reached out yesterday about a formula to help me return the fiscal year and quarter based on the date column. My fiscal year started November 1st.

  • The formula provided to me was =IF(ISDATE(Date@row), "FY" + RIGHT(IF(MONTH(TODAY()) > 6, YEAR(TODAY()) + 1, YEAR(TODAY())), 2) + "-Q" + IF(OR(MONTH(Date@row) = 1, MONTH(Date@row) >= 11), 1, IF(MONTH(Date@row) = 2, 2, IF(MONTH(Date@row) = 3, 2, IF(MONTH(Date@row) = 4, 2, IF(MONTH(Date@row) = 5, 3, IF(MONTH(Date@row) = 6, 3, IF(MONTH(Date@row) = 7, 3, 4))))
  • I entered the formula and it gives me a return of #UNPARSEABLE. Also, it is returning the value on a different column than where I entered the formula and I can't enter the formula on the date column since it is just a date column and it won't let me. attached is a picture of what my set up looks like so you can get a better understanding of what I need.


Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Diego Casillas Zaragoza

    I believe I've been helping you on this thread, here!

    Here's the updated formula with November as the start of your quarter:

    ="Q" + IF(OR(MONTH(Date@row) >= 11, MONTH(Date@row) = 1), "1FY", IF(MONTH(Date@row) >= 8, "4FY", IF(MONTH(Date@row) >= 5, "3FY", "2FY"))) + IF(MONTH(Date@row) >= 11, RIGHT(INT(YEAR(Date@row) + 1), 2), RIGHT(YEAR(Date@row), 2))

    You should be able to copy/paste the formula at the end, as long as the Date column is titled "Date", otherwise you may need to adjust the column name in the formula.

    If you get an error, can you post a screen capture with the formula open in the cell, showing if it's highlighting the correct Date column or not?

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!