Simplify this formula to enter quarters based on date range.

mkilci

Hello,

I need help simplifying this formula. I need to enter a Quarter based on a date,

For example, if the date is Jan 1 2020, enter Q120 in the cell. If April 1 2020 than enter Q220, and so on.

I am using this formula, which is making the sheet to complicated and possibly impacting the performance.

The crazy formula is:

=IF(AND([Close Date]@row >= DATE(2019, 6, 1), [Close Date]@row <= DATE(2019, 7, 30)), "19-Q3", IF(AND([Close Date]@row >= DATE(2019, 8, 1), [Close Date]@row <= DATE(2019, 10, 31)), "19-Q4", IF(AND([Close Date]@row >= DATE(2019, 11, 1), [Close Date]@row <= DATE(2020, 1, 31)), "20-Q1", IF(AND([Close Date]@row >= DATE(2020, 2, 1), [Close Date]@row <= DATE(2020, 4, 30)), "20-Q2", IF(AND([Close Date]@row >= DATE(2020, 5, 1), [Close Date]@row <= DATE(2020, 7, 31)), "20-Q3", IF(AND([Close Date]@row >= DATE(2020, 8, 1), [Close Date]@row <= DATE(2020, 10, 31)), "20-Q4",IF(AND([Close Date]@row >= DATE(2020, 11, 1), [Close Date]@row <= DATE(2021, 1, 31)), "21-Q1")))))))

How can I use a lookup table of some sort to simplify this. The other issue is that I need to rewrite the formula every 3 months to extend the date range.

Thanks in advance.

Andrée Starå

Hi,

You don't have to use a table if you don't want to. If you start with the latest Quarter and go backward, you need only a simple multiple IF statement as below.

Try something like this.

=IF([Close Date]@row >= DATE(2020; 11; 1); "21-Q1"; IF([Close Date]@row >= DATE(2020; 8; 1); "20-Q4"; IF([Close Date]@row >= DATE(2020; 5; 1); "20-Q3"; IF([Close Date]@row >= DATE(2020; 2; 1); "20-Q2"; IF([Close Date]@row >= DATE(2019; 11; 1); "20-Q1"; IF([Close Date]@row >= DATE(2019; 8; 1); "19-Q4"; IF([Close Date]@row >= DATE(2019; 6; 1); "19-Q3"; "")))))))

The same version but with the below changes for your and others convenience.

=IF([Close Date]@row >= DATE(2020, 11, 1), "21-Q1", IF([Close Date]@row >= DATE(2020, 8, 1), "20-Q4", IF([Close Date]@row >= DATE(2020, 5, 1), "20-Q3", IF([Close Date]@row >= DATE(2020, 2, 1), "20-Q2", IF([Close Date]@row >= DATE(2019, 11, 1), "20-Q1", IF([Close Date]@row >= DATE(2019, 8, 1), "19-Q4", IF([Close Date]@row >= DATE(2019, 6, 1), "19-Q3", "")))))))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma." 

Would that work?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer! 

mkilci

Thanks for the reply Andree. I would like to use a table for this formula. Is that possible?

Paul Newcome

You would set up your table so that the last day of that quarter is displayed. I suggest setting it up in chronological order just for simplicity when updating, but this will work regardless of the order. Then in the column next to those dates, you would enter the values that you want populated for the quarter ending on that date. I will use Calendar year 2020 as an example.

Then in the column where you want to pull the quarter value, you would use a formula like this:

=INDEX(Index:Index, MATCH(MIN(COLLECT(Match:Match, Match:Match, @cell >= [Date Column]@row)), Match:Match, 0))

Since you are referencing whole columns for the table, you can add/change/delete any values you need on the table without having to update the formula itself.

mkilci

Thanks for the reply Paul. I am fairly new to Smartsheets and I am getting the #UNPARSABLE error.

What am I doing wrong? Thanks again

Paul Newcome

Try removing the spaces from the ranges. I also suggest referencing the entire column if you are able so that you do not need to update the ranges in the formula if you extend your table beyond 10 rows.

mkilci

Here is what the formula reads (after updating the ranges to column):

INDEX(Index:Index, MATCH(MIN(COLLECT(Match:Match, Match:Match,[Column4]1>=[Date Column]@row)),Match:Match, 0))

What spaces are you referring to? Is it in the formula?

Paul Newcome

In the screenshot you provided, it shows as

Index1 :Index10

instead of

Index1:Index10

It is the same for all of your ranges.

Andrée Starå

@mkilci

Happy to help! 

I saw that Paul answered already!

Let me know if I can help with anything else!

Best, 

Andrée 

Morgan Frost

Hi all, Nice formulas!

@Paul Newcome

I'm trying to do the same thing but with a cross-sheet reference so I can keep the reference table elsewhere. Instead of Quarters I want the sprint name returned. How can I include the cross-sheet references in my formula? Since it's already a range can I tried to plug in the reference instead of the range format or should I be trying to get the colon and the [ ] in there somehow? I tried but it got so messy and seemed redundant--also got errors that way.

I am getting "#incomplete argument set" with this attempt to copy your formula and replace ranges with cross-sheet references:

=INDEX({Sprint Dates Reference (Sprint)}, MATCH(MIN(COLLECT({Sprint Dates Reference(End)}, {Sprint Dates Reference(End)}, @cell >= [Planned Completion]@row), {Sprint Dates Reference(End)}, 0)))

The reference sheet -- all of our sprint cycles (the number with the sprint name is the ID #, so our 20th sprint was April 2019), I am focusing the formula on the End column so I don't miss any weekend items. For example something on 4/26/2019 should show up as "21-May" if my formula worked.

The sheet with the formula in it - I want to display which sprint based on the planned completion date.

Maybe the color-coding will help?

=INDEX(Index:Index, << Ok I get this is supposed to be the column with the value I want returned--the sprint.

MATCH(MIN(COLLECT(Match:Match, << This would be the full range of the reference column dates

Match:Match, << this will be the same thing as the above line I think - need it bc "collect" format

@cell >= [Date Column]@row)), << this points the to sheet where end user enters date

Match:Match, << no idea what this is doing here

0)) << not sure, something about which column to grab, I may need a 1 or 2?

Paul Newcome

It is just a misplaced closing parenthesis. You need to close out the MIN and COLLECT functions together.

=INDEX({Sprint Dates Reference (Sprint)}, MATCH(MIN(COLLECT({Sprint Dates Reference(End)}, {Sprint Dates Reference(End)}, @cell >= [Planned Completion]@row)), {Sprint Dates Reference(End)}, 0))

Morgan Frost

@Paul Newcome Thank you, that did it! Ugh of course something so simple would trip me up after all that complexity. Love the functionality this gives me.

L_123

="Q" + INT((MONTH(Date@row) + 2) / 3) + RIGHT(Date@row, 2)

Did this before reading the date of the original question hahah. anyways if someone else is trying to optimize the first question here is a bit shorter solution without a helper table.

Katye Reed

Hello,

I am trying to accomplish something similar based on fiscal year but the results are not returning the correct dates.

=IFERROR(INDEX(Index:Index, MATCH(MIN(COLLECT(Match:Match, Match:Match, @cell >= [SP&T Disposition Date]@row)), Match:Match, 0)), "")

Match Column Index Column

7/1/19 ---- FY 20

7/1/20 ---- FY 21

Paul Newcome

@Katye Reed What is it returning if it not returning the correct FY?

Katye Reed

Please see attached. If i put in June 2020 it is displaying FY 21 instead of FY 20. I'm sure it's something silly but for the life of me I can't figure out what. Thanks so much for the assistance.