Displaying Multiple Quarters based on Start and End Date

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.

Find more posts tagged with

Formulas

Accepted answers

All comments

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:

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)

Screenshot 2023-12-01 at 11.05.13 AM.png

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.