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!

Find more posts tagged with

Formulas

IT & Operations

Project Management

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…