I have a formula to return the quarter based on a date. I would like to add the year also.

Options

Here is the formula, right out of this community (THANK YOU!!):

=IF(MONTH([Start Date]@row) < 4, "Q1", IF(MONTH([Start Date]@row) < 7, "Q2", IF(MONTH([Start Date]@row) < 10, "Q3", "Q4")))

Here is my awkward work around -Start Date | Start Quarter from formula above | Start Year from YEAR formula | JOIN Quarter and Year. Is there a way to do all of this in one formula?

Thanks ahead of time. πŸ‘‹

Best Answers

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭
    edited 08/15/23 Answer βœ“
    Options

    Hello @Celia Gust

    Hope you are well. Would this work - you would need to update the Date Column to what you have in your sheet:

    ="Q" + IF(MONTH([Date Column]@row) >= 10, "4 - ", IF(MONTH([Date Column]@row) >= 7, "3 - ", IF(MONTH([Date Column]@row) >= 4, "2 - ", "1 - "))) + YEAR([Date Column]@row)

    Peggy

  • Celia Gust
    Celia Gust ✭✭✭
    Answer βœ“
    Options

    Peggy, this works beautifully. Thank you so much. I just took out the dash, only because the excel I'm trying to replace had it formatted without a dash. Now I think I'll go watch some videos on formulas so I can be more independent. πŸ˜ƒ Have a great day!

Answers

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭
    edited 08/15/23 Answer βœ“
    Options

    Hello @Celia Gust

    Hope you are well. Would this work - you would need to update the Date Column to what you have in your sheet:

    ="Q" + IF(MONTH([Date Column]@row) >= 10, "4 - ", IF(MONTH([Date Column]@row) >= 7, "3 - ", IF(MONTH([Date Column]@row) >= 4, "2 - ", "1 - "))) + YEAR([Date Column]@row)

    Peggy

  • Celia Gust
    Celia Gust ✭✭✭
    Answer βœ“
    Options

    Peggy, this works beautifully. Thank you so much. I just took out the dash, only because the excel I'm trying to replace had it formatted without a dash. Now I think I'll go watch some videos on formulas so I can be more independent. πŸ˜ƒ Have a great day!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!