Generating previous quarter from current date


Hi there,

I am trying to get a formula that will generate what the previous quarter is for our fiscal year which starts in October.

So for today April 2024 which is in our Q3, the previous Quarter would be FY24 Q2. If we were in October 2023, the previous Quarter would be FY23 Q4. If we were in January 2024, the previous Quarter would be FY24 Q1.

I am having a lot of trouble figuring out the logic for this, so any help would be appreciated.


  • bisaacs
    bisaacs ✭✭✭✭✭

    Hey @Derek Stiles,

    You could use an IF/AND statement to do this I think:

    "=IF(AND(TODAY() > 3/31/2024, TODAY() < 6/30/2024), "FY24 Q2", IF(AND(TODAY() > 12/31/2023, TODAY() < 3/31/2024, "FY24 Q1", IF(AND(TODAY......)

    Do this for all the start/end dates of your quarters and I think it should work.

    Hope this helps!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • Derek Stiles

    Thank you bisaacs. I need something a little more general because this list will continue to have rows added to it indefinitely and it isn't tenable for me to create an iff statement for each quarter for every year in the future. It would need to sort of figure out what year it is, like (Today() >3/31/[CURRENT YEAR]... "FY[CURRENT YEAR] Q2"

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    =IF(MONTH(TODAY()) <= 3, "FY" + RIGHT(YEAR(TODAY()), 2) + " Q1", IF(MONTH(TODAY()) <= 6, "FY" + RIGHT(YEAR(TODAY()), 2) + " Q2", IF(MONTH(TODAY()) <= 9, "FY" + RIGHT(YEAR(TODAY()), 2) + " Q3", "FY" + RIGHT(YEAR(TODAY()), 2) + " Q4")))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!