Create way to reflect quarter?

Karen Webber · 09/05/24

Dear brains, I have the following setup that I need some suggestions on:

I have a database that captures planned travel across the year, and is captured just before every quarter for the upcoming quarter plus one month (so for example, the db will be populated in September for Q2 travel from October - January, but January will also be included as a standard for captures in Q3). The Q field is a dropdown and the options something like this (they get updated as we go, obviously):

Q2: Oct 24-Jan 2025
Q3: Jan - April 2025
Q4: April - July 2025

There are two reports that I generate from this - one for requests that haven't been approved yet, and one for approved requests. Both reports include the coming quarter, but also have to include the current quarter if we're still in that overlap month. In addition, I don't want quarters from previous years to come up (otherwise I would just identify the Q from the dropdown and work by some sort of Vlookup table). Currently I manually change the filters on the reports to move one quarter on, right after the current batch of events have been captured, but it's a pain and I'm sure there has to be a way to make the process automatic. My brain just isn't cooperating.

Any suggestions for me on an automated way to identify the current and upcoming quarters?

Brian_Richardson · 09/09/24

Maybe a checkbox field to identify "include in report"?

=IF(AND(MONTH(TODAY())<=4,[Q]@row="Q4"),true,if(AND(MONTH(TODAY())⇐7,[Q]@row="Q2"),true,if(AND(MONTH(TODAY())⇐10,[Q]@row="Q3"),true,if(and(or(month(today())⇐12,month(today())=1),[Q]@row="Q4"),true))))

Then in your reports just filter for that include checkbox and it should automatically pull in the right items.

Brian_Richardson · 09/09/24

Maybe a checkbox field to identify "include in report"?

=IF(AND(MONTH(TODAY())<=4,[Q]@row="Q4"),true,if(AND(MONTH(TODAY())⇐7,[Q]@row="Q2"),true,if(AND(MONTH(TODAY())⇐10,[Q]@row="Q3"),true,if(and(or(month(today())⇐12,month(today())=1),[Q]@row="Q4"),true))))

Then in your reports just filter for that include checkbox and it should automatically pull in the right items.

Karen Webber · 09/16/24

https://community.smartsheet.com/discussion/comment/453164#Comment_453164

Thanks for the suggestion, @Brian_Richardson. I tried it and it works perfectly, save for the fact that it's still pulling in the relevant quarters from previous years. I'll play around with CONTAINS to see if I can IF by the Q year as well.

Many thanks!

Create way to reflect quarter?

Best Answer

Answers

Help Article Resources

Categories