Help with formula to populate a quarter (text) based off date field.
Hello! I've searched many posts on the forum and can't find a solution. I'm at my wits end, any help would be very much appreciated!
I am looking for a formula that will show a text result of Q#-YYYY based on an entry in a date field ("Expected Delivery Date", which is using the standard SmartSheet Date column.
I can't round up, I can't see how to do an IF statement and not get an unparseable error. Any help or advice on where I should be looking is appreciated!
Comments
-
="Quarter#"+If(weeknumber(Date@row) < 13,1,if(weeknumber(date@row)<26,2,if(weeknumber(date@row)<39,3,4)))+"-"+year(date@row)
Or something similar will work.
-
Thanks for the prompt reply.
I'm not using week numbers - this is coming off a date field. Perhaps I'm just missing something in your explanation as I'm very new to smartsheet.
-
Weeknumber is a formula in smartsheet that references a date and returns the number of the week that corresponds.
The formula:
weeknumber(date(2019,1,1))
Would return a 1, as january 1, 2019 is the first week in the year 2019.
52/4 = 13
Therefore every 13 weeks we have a new quarter.
if current week is <= 13 it must be the first quarter
if that isn't true then the number must be greater than 13
if the number is <= 26 it must be in the second quarter as we have already proven the number is greater than 13
If the number is not <= 26, it must be greater than 26 or the third or fourth quarter
if the number is <= 39 it must be the third quarter. If not the value has to be greater than 39. The only quarter that has more than 39 weeks is the fourth quarter.
I should have placed an equal sign in the original edit, but smartsheet wouldn't let me edit the formula. I've posted the updated below:
="Quarter"+If(weeknumber(Date@row) <= 13,1,if(weeknumber(date@row)<=26,2,if(weeknumber(date@row)<=39,3,4)))+"-"+year(date@row)
I supposed it depends on how you want to handle quarters, I suppose this could also be handled using the month formula, but the logic is the same. Thinking about it a little more deeply the month formula is probably more practical and more consistent. I've posted it below.
="Quarter"+If(month(Date@row) <= 3,1,if(month(date@row)<=6,2,if(month(date@row)<=9,3,4)))+"-"+year(date@row)
-
Thank you so much!!! I really appreciate the detail! I *think* I found a solution as well but I'm going to try yours - I really, really appreciate the thoughtful response.
My possible solution
="Q" + INT((MONTH([Expected Delivery Date]2) + 2) / 3) + " - " + YEAR([Expected Delivery Date]2, 1)
-
Yeah that is even cleaner. That should work even better than what I typed out.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!