Formula to Find the Start/End Timeline

Hello SS Community,

I'm trying to find a formula to insert in the sheet summary that brings the earliest Quarter and Fiscal Year (FY) and the last/furthest quarter and FY.

Thank you in advance.



Tags:

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    My apologies. I had a misplaced parenthesis. Try this corrected one:

    ="FY" + LEFT(MIN([Start Helper]:[Start Helper]), 2) + " " + "Q" + RIGHT(MIN([Start Helper]:[Start Helper]))


    And yes. It should be 3 digits that represent "YYQ"

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    @maltaee You could try something like this:

    ="FY" + RIGHT(IF(TODAY()> DATE(YEAR(TODAY()), 7, 1), YEAR(TODAY()) + 1, YEAR(TODAY())), 2) + " Q" + IF(TODAY()>= DATE(YEAR(TODAY()), 10, 1), "2", IF(TODAY()>= DATE(YEAR(TODAY()), 7, 1), "1", IF(TODAY()>= DATE(YEAR(TODAY()), 4, 1), "4", "3")))

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You'll need to insert two helper text/number columns (that can be hidden after setting everything up).

    The first would be a helper for the Start:

    =VALUE(RIGHT([Start FY]@row, 2) + "" + RIGHT([Start Quarter]@row))


    The second would be a helper for the End. You would use the same formula as above except reference the end fy and quarter columns.


    Then the earliest FY/Quarter would be:

    ="FY" + LEFT(MIN([Start Helper]:[Start Helper], 2) + " " + "Q" + RIGHT(MIN([Start Helper]:[Start Helper]))


    The latest FY/Quarter would be the same as above but reference the End Helper and switch the MIN functions to MAX functions.

  • maltaee
    maltaee ✭✭✭✭✭

    Thanks so much. I'm getting "#Invalid Operation" in the sheet summary.

    Does the Start Helper pull the right thing? it shows 3 digits.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    My apologies. I had a misplaced parenthesis. Try this corrected one:

    ="FY" + LEFT(MIN([Start Helper]:[Start Helper]), 2) + " " + "Q" + RIGHT(MIN([Start Helper]:[Start Helper]))


    And yes. It should be 3 digits that represent "YYQ"

  • maltaee
    maltaee ✭✭✭✭✭

    Sorry to bother you. It still shows an invalid operation.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Do you have any blanks? If so, try...


    Wrap the helper column formulas in an IFERROR like so:

    =IFERROR(VALUE(RIGHT([Start FY]@row, 2) + "" + RIGHT([Start Quarter]@row)), "")


    And adjust the final formula like this:

    ="FY" + LEFT(MIN(COLLECT([Start Helper]:[Start Helper], [Start Helper]:[Start Helper], @cell <> "")), 2) + " " + "Q" + RIGHT(MIN(COLLECT([Start Helper]:[Start Helper], [Start Helper]:[Start Helper], @cell <> "")))

  • maltaee
    maltaee ✭✭✭✭✭

    Paul,

    Thanks for catching that. Actually, there are TBD, not blanks, in many cells. I have to wait until they change it unless there is a workaround.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    That would end up throwing the same error, so the IFERROR and COLLECT fixes above should still do the trick for you.

  • maltaee
    maltaee ✭✭✭✭✭

    Thanks, Paul.

    It worked after changing the TBDs cells.

    Last question. Is there a formula to display the current quarter and fiscal year? Ex. It should show FY23 Q2 now until Dec 31, 2022 and then FY23 Q3



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    @maltaee You could try something like this:

    ="FY" + RIGHT(IF(TODAY()> DATE(YEAR(TODAY()), 7, 1), YEAR(TODAY()) + 1, YEAR(TODAY())), 2) + " Q" + IF(TODAY()>= DATE(YEAR(TODAY()), 10, 1), "2", IF(TODAY()>= DATE(YEAR(TODAY()), 7, 1), "1", IF(TODAY()>= DATE(YEAR(TODAY()), 4, 1), "4", "3")))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!