Help with formula to populate a quarter (text) based off date field.

MandyKelly

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!

L_123

="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. 

MandyKelly

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.

 

 

L_123

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)

MandyKelly

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)

L_123

Yeah that is even cleaner. That should work even better than what I typed out.