Formula to pull in Quarter from a reference sheet

Raquel Love

I have created a reference sheet called Quarter Reference Sheet that has the quarters based on a start and end date:

I have another sheet where I want to create a formula that pulls in the "Quarter" column above based on if the "Completed Date" column falls somewhere between the Start Date and End Date in the reference sheet. Example, if the Completed Date column has a date of April 1, 2020, I want the formula to look at the information in the Start Date and End Date columns in the reference sheet and pull in the value of the appropriate matching row in the Quarter column - in this example, Q1 2020.

Is this an index/match formula? If so, does anyone have a formula I could try using here that might work?

Paul Newcome

Ah. Ok. Sorry about that. Let's see... Give this one a try...

=INDEX({Table Sheet Quarter Column}, MATCH(MAX(COLLECT({Table Sheet Start Date Column}, {Table Sheet Start Date Column}, @cell <= [Completed Date]@row)), {Table Sheet Start Date Column}, 0))

The basic rundown of how this works is we pull the largest date that is less than or equal to the [Completed Date]. Then we match on that to determine which row from the Quarter column we need to pull.

Paul Newcome

You could skip out on using a table with cross sheet references in favor of automating directly in the sheet.

="Q" + IF(OR(MONTH([Completed Date]@row) = 1, MONTH([Completed Date]@row) >= 11), "4", IF(MONTH([Completed Date]@row <= 4, "1", IF(MONTH([Completed Date]@row) <= 7, "2", "3"))) + " " + (YEAR([Completed Date]@row) - IF(MONTH([Completed Date]@row) = 1, 1))

Raquel Love

Thanks Paul. The dates for our quarters differ each year, so this formula isn't working - for example Q1 2020 ended April 3, 2020 but Q1 in 2021 ended May 1, 2021. This is why I wanted to use a reference table so I could pull the exact dates based on quarter start and end dates through the next 5 years. Is there a formula I could use that would pull in the quarters based on a date range in another reference sheet?

Paul Newcome

Ah. Ok. Sorry about that. Let's see... Give this one a try...

=INDEX({Table Sheet Quarter Column}, MATCH(MAX(COLLECT({Table Sheet Start Date Column}, {Table Sheet Start Date Column}, @cell <= [Completed Date]@row)), {Table Sheet Start Date Column}, 0))

The basic rundown of how this works is we pull the largest date that is less than or equal to the [Completed Date]. Then we match on that to determine which row from the Quarter column we need to pull.

Raquel Love

Thanks Paul - this formula worked perfectly! :)

Paul Newcome

Happy to help. 👍️