Create way to reflect quarter?
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?
Best Answer
-
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.
Answers
-
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.
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!