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
Check out the Formula Handbook template!
