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

edited 12/09/19 in Formulas and Functions

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
    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
    L_123 ✭✭✭✭✭✭

    Weeknumber is a formula in smartsheet that references a date and returns the number of the week that corresponds.

    The formula:


    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
    L_123 ✭✭✭✭✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!