How to autopopulate cell based on date in another cell
Hello!
I am trying to automate adding an ID in my sheet for "2023Q4" and then of course next year for 2024Q1, etc. Currently, I am adding the ID manually and am wondering if there is a way to create a formula that will tell me IF the submission date is between the dates of a particular quarter, the ID updates to the specific quarter ID I need. I found this article (https://community.smartsheet.com/discussion/42386/return-value-if-between-dates) but I keep getting "unparseable" responses and can't seem to figure it out. I just need to know that if an item is between specific quarter dates (1/1-3/31, 4/1-6/30, 7/1-9/30, and 10/1-12/31) then it gets its proper ID for my metrics sheet.
I tried: IF(AND([DATE SUBMITTED]@row >= DATE(2023, 10, 1), [DATE SUBMITTED]@row <= DATE(2023, 12, 31)), "2023Q4" just to see if I can get the rest of this year figured out, but can't seem to get past the unparseable error. I have a feeling it's going to be a longer formula than I'm thinking (in order to capture all dates in the past/future) but am honestly not sure. I am a newbie at this so thank you in advance for your help!
The ID column is currently set up as a text/number column and the date of submission is a date column.
Answers
-
Your formula seems to be fine. Make sure that your columns are actually set as Date.
...
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!