Displaying Multiple Quarters based on Start and End Date

K.Honey

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.

Danielle Arteaga

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

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")))))))))))))))))))))