How to autopopulate cell based on date in another cell

Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!