Concatenate Integer + Words?

steph_aw

I work in events. I group my tasks by how many months out the task is from the event date (1 month out, 2 months out, etc.). I'm wondering how I can create the `1 month out from event` in field using a formula.

I'm coming from the world of Airtable where I could calculate how many months a Due Date was from an Event Start Date by doing something like DATETIME_DIFF({Event Start Date}, {Due Date}, 'M') which would return the result of the equation in the number of months between the dates. I could then add on words to make it say 2 Months out from the Event. CONCATENATE(DATETIME_DIFF({Event Start Date}, {Due Date}, 'M'), " ", "months", " ", "before the event"). Then I would group by that field to lay out my project plan.

Is there a way to recreate this in Smartsheet? I know that the formula I used in Airtable is not available in Smartsheet, so I'm wondering if there's another way to handle.

steph_aw

For anyone that finds this question, I figured this out!

=ROUND(-(NETDAYS([Event Start Date (from Flagship Event)]@row, [Due Date Calculated USE]@row) / 365) * 12) + " months" + " from" + " the " + " event"

I used the formula listed by @Toufong Vang on this thread: https://community.smartsheet.com/discussion/80255/how-to-count-the-months-between-2-dates and then added on the words using the + and " " around the words I wanted to display.

I put a - in front of my Netdays formula so that it would say "7 months from the event" instead of "-7 months from the event". That's just my personal preference, it's not necessary to make this work.

steph_aw

For anyone that finds this question, I figured this out!

=ROUND(-(NETDAYS([Event Start Date (from Flagship Event)]@row, [Due Date Calculated USE]@row) / 365) * 12) + " months" + " from" + " the " + " event"

I used the formula listed by @Toufong Vang on this thread: https://community.smartsheet.com/discussion/80255/how-to-count-the-months-between-2-dates and then added on the words using the + and " " around the words I wanted to display.

I put a - in front of my Netdays formula so that it would say "7 months from the event" instead of "-7 months from the event". That's just my personal preference, it's not necessary to make this work.