Simpler Fiscal Year and Quarter Formula

Hi,

Admittedly, I'm very new when it comes to spreadsheet formulas. I was given this formula for fiscal year and quarter calculations but I was wondering if there was a simpler way to do it. Also, it's in a calendar year format and our fiscal year is July 1 to June 30. I appreciate your help.


=IF(ISDATE([Completion Target]@row), "FY" + IF(MONTH([Completion Target]@row) = 1, RIGHT(YEAR([Completion Target]@row), 2), RIGHT(YEAR([Completion Target]@row) + 1, 2)) + "-Q" + IF(OR(MONTH([Completion Target]@row) = 1, MONTH([Completion Target]@row) >= 11), 4, IF(MONTH([Completion Target]@row) >= 8, 3, IF(MONTH([Completion Target]@row) >= 5, 2, 1))))


Thanks!

Ashley Ferguson, PMP

IS Project Manager | St. Joseph’s/Candler

http://linkedin.com/in/ashley-ferguson-399396a

Answers

  • Neil Watson
    Neil Watson ✭✭✭✭✭✭
    edited 11/10/21

    Hi Ashley, we use this for the fiscal year.

    ="FY" + RIGHT(IF(MONTH(TODAY()) > 6, YEAR(TODAY()) + 1, YEAR(TODAY())), 2)

    Which shows (for this fiscal year) FY22.

    So

    =IF(ISDATE([Completion Target]@row), "FY" +RIGHT(IF(MONTH(TODAY()) > 6, YEAR(TODAY()) + 1, YEAR(TODAY())), 2) + "-Q" + IF(OR(MONTH([Completion Target]@row) = 1, MONTH([Completion Target]@row) >= 11), 4, IF(MONTH([Completion Target]@row) >= 8, 3, IF(MONTH([Completion Target]@row) >= 5, 2, 1))))

  • Thank you so much. Any thoughts on what I need to adjust so it reflects our quarters properly?

    Q1: July/August/September

    Q2: October/November/December

    Q3: January/February/March

    Q4: April/May/June

    Ashley Ferguson, PMP

    IS Project Manager | St. Joseph’s/Candler

    http://linkedin.com/in/ashley-ferguson-399396a

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!