Displaying Multiple Quarters based on Start and End Date

Options

Hi - I would like to request assistance with developing an equation. The equation needs to display one or many Quarters based on the 'Start' and 'End' date of a task. Many of the equations I have tried display the quarter in which the events starts. However, I would like the equation to support the following: If an event begins on 3-Jan and ends on 12-Dec, I would like the 'Quarter' field to display: Q1, Q2, Q3, Q4. As such, the equation needs to account for the end date as well as the start date.

Tags:

Answers

  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭
    Options

    Here is one approach (which is a bit of a guess because I'm not sure how your fiscal year runs or what your sheet structure looks like ...):

    [Start Date] column is set to Date column type.

    Create a [Start Quarter] column and use this formula (BUT REPLACE THE DATE RANGES WITH YOUR QUARTER START and END dates):

    =IF(AND([Start Date]@row >= DATE(2023, 7, 23), [Start Date]@row <= DATE(2023, 10, 28)), "Q1", IF(AND([Start Date]@row >= DATE(2023, 10, 29), [Start Date]@row <= DATE(2024, 1, 27)), "Q2", IF(AND([Start Date]@row >= DATE(2024, 1, 28), [Start Date]@row <= DATE(2024, 4, 27)), "Q3", IF(AND([Start Date]@row >= DATE(2024, 4, 28), [Start Date]@row <= DATE(2024, 7, 27)), "Q4", "Future"))))

    [End Date] column is set to Date column type.

    Create an [End Quarter] column and use this formula (again changing the dates as needed):

    =IF(AND([End Date]@row >= DATE(2023, 7, 23), [End Date]@row <= DATE(2023, 10, 28)), "Q1", IF(AND([End Date]@row >= DATE(2023, 10, 29), [End Date]@row <= DATE(2024, 1, 27)), "Q2", IF(AND([End Date]@row >= DATE(2024, 1, 28), [End Date]@row <= DATE(2024, 4, 27)), "Q3", IF(AND([End Date]@row >= DATE(2024, 4, 28), [End Date]@row <= DATE(2024, 7, 27)), "Q4", "Future"))))

    Create an [All Quarters] column as a Dropdown List column type. Populate the drop down options with Q1, Q2, Q3, Q4. Use this formula:

    =IF(AND([Start Quarter]@row = "Q1", [End Quarter]@row = "Q1"), "Q1", IF(AND([Start Quarter]@row = "Q1", [End Quarter]@row = "Q2"), "Q1, Q2", IF(AND([Start Quarter]@row = "Q1", [End Quarter]@row = "Q3"), "Q1, Q2, Q3", IF(AND([Start Quarter]@row = "Q1", [End Quarter]@row = "Q4"), "Q1, Q2, Q3, Q4", IF(AND([Start Quarter]@row = "Q2", [End Quarter]@row = "Q2"), "Q2", IF(AND([Start Quarter]@row = "Q2", [End Quarter]@row = "Q3"), "Q2, Q3", IF(AND([Start Quarter]@row = "Q2", [End Quarter]@row = "Q4"), "Q2, Q3, Q4", IF(AND([Start Quarter]@row = "Q3", [End Quarter]@row = "Q3"), "Q3", IF(AND([Start Quarter]@row = "Q3", [End Quarter]@row = "Q4"), "Q3, Q4", "Q4")))))))))

    NOTE: You can hide the [Start Quarter] and [End Quarter] columns, because they are really just "helper" columns to make your formulas more manageable.

    SECOND NOTE: If you want your quarters to display individually (without a comma separator), you can use CHAR(10) in your formula. Here's more on how CHAR(10) works: https://help.smartsheet.com/function/char#:~:text=You%20can%20use%20CHAR(10,line%20break%20within%20a%20formula.&text=To%20display%20a%20line%20break,cell%20formatting%2C%20see%20Formatting%20Options.

    Here's an example of what CHAR(10) does (see the second row - the options are stacked instead of comma delimited)


  • Kristian W. Honey
    Options

    Danielle - The above was extremely helpful. I modified the equation slightly to support my "Start" and "Finish" column headers (see below). Also, added in a few additional controls since my stakeholders sometime include prior (or future) year activities on the annual plan. Thanks again!

    START QUARTER

    =IF(AND(Start@row >= DATE(2023, 1, 1), Start@row < DATE(2024, 1, 1)), "Prior Year", IF(AND(Start@row >= DATE(2024, 1, 1), Start@row < DATE(2024, 4, 1)), "Q1", IF(AND(Start@row >= DATE(2024, 4, 1), Start@row < DATE(2024, 7, 1)), "Q2", IF(AND(Start@row >= DATE(2024, 7, 1), Start@row < DATE(2024, 10, 1)), "Q3", IF(AND(Start@row >= DATE(2024, 10, 1), Start@row < DATE(2025, 1, 1)), "Q4", "Future Year")))))

     

    END QUARTER

    =IF(AND(End@row >= DATE(2023, 1, 1), End@row < DATE(2024, 1, 1)), "Prior Year", IF(AND(End@row >= DATE(2024, 1, 1), End@row < DATE(2024, 4, 1)), "Q1", IF(AND(End@row >= DATE(2024, 4, 1), End@row < DATE(2024, 7, 1)), "Q2", IF(AND(End@row >= DATE(2024, 7, 1), End@row < DATE(2024, 10, 1)), "Q3", IF(AND(End@row >= DATE(2024, 10, 1), End@row < DATE(2025, 1, 1)), "Q4", "Future Year")))))


    "Quarterly Events"

    =IF(AND([Start Quarter]@row = "Q1", [End Quarter]@row = "Q1"), "Q1", IF(AND([Start Quarter]@row = "Q1", [End Quarter]@row = "Q2"), "Q1, Q2", IF(AND([Start Quarter]@row = "Q1", [End Quarter]@row = "Q3"), "Q1, Q2, Q3", IF(AND([Start Quarter]@row = "Q1", [End Quarter]@row = "Q4"), "Q1, Q2, Q3, Q4", IF(AND([Start Quarter]@row = "Q1", [End Quarter]@row = "Future Year"), "Q1, Q2, Q3, Q4, Future Year", IF(AND([Start Quarter]@row = "Prior Year", [End Quarter]@row = "Prior Year"), "Move to Prior Year", IF(AND([Start Quarter]@row = "Prior Year", [End Quarter]@row = "Q1"), "Q1", IF(AND([Start Quarter]@row = "Prior Year", [End Quarter]@row = "Q2"), "Q1, Q2", IF(AND([Start Quarter]@row = "Prior Year", [End Quarter]@row = "Q3"), "Q1, Q2, Q3", IF(AND([Start Quarter]@row = "Prior Year", [End Quarter]@row = "Q4"), "Q1, Q2, Q3, Q4", IF(AND([Start Quarter]@row = "Q2", [End Quarter]@row = "Q2"), "Q2", IF(AND([Start Quarter]@row = "Q2", [End Quarter]@row = "Q3"), "Q2, Q3", IF(AND([Start Quarter]@row = "Q2", [End Quarter]@row = "Q4"), "Q2, Q3, Q4", IF(AND([Start Quarter]@row = "Q3", [End Quarter]@row = "Q3"), "Q3", IF(AND([Start Quarter]@row = "Q4", [End Quarter]@row = "Q4"), "Q4", IF(AND([Start Quarter]@row = "Q3", [End Quarter]@row = "Q4"), "Q3, Q4", IF(AND([Start Quarter]@row = "Q1", [End Quarter]@row = "Future Year"), "Q1, Q2, Q3, Q4, Future Year", IF(AND([Start Quarter]@row = "Q2", [End Quarter]@row = "Future Year"), "Q2, Q3, Q4, Future Year", IF(AND([Start Quarter]@row = "Q3", [End Quarter]@row = "Future Year"), "Q3, Q4, Future Year", IF(AND([Start Quarter]@row = "Q4", [End Quarter]@row = "Future Year"), "Q4, Future Year", IF(AND([Start Quarter]@row = "Future Year", [End Quarter]@row = "Future Year"), "Needs Dates", "ERROR")))))))))))))))))))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!