Best Of

Hi @John Stanik

Will your text entry always have the exact same format of mm/dd/yy?

You can use the DATE function to translate numbers into Dates, with the syntax being DATE(YYYY, MM, DD).

In this instance, we'll want to use the VALUE Function around each number set we're grabbing, like so:

DATE(VALUE(20 + YY), VALUE(MM), VALUE(DD))

Then we can use the RIGHT Function, MID Function, and LEFT Function to grab different elements of your text string.

DATE(VALUE(20 + RIGHT(YY)), VALUE(LEFT(MM)), VALUE(MID(DD)))

For the MID portion, we'll need to use a FIND Function to find the starting position (a number) of whatever it is looking for, in this case a "/". The formula uses the number it finds as a starting position in the text string.

Full Formula:

=DATE(VALUE(20 + RIGHT([Text Column]@row, 2)), VALUE(LEFT([Text Column]@row, 2)), VALUE(MID([Text Column]@row, FIND("/", [Text Column]@row) + 1, 2)))

You'll need to enter the formula into a Date Column. Let me know if this works and makes sense!

Cheers,

Genevieve

I am wondering if there is a way to trigger a task list to start when criteria is met?

The task list is built and made into a template. I am wanting to trigger the activation or start and rename of this when the contract column in my schedule is changed to NTP. The file name would change to my Job Code which would be @row. Then the task template needs one date based off job start@row to activate all tasks.

Is this a possibility?

Slight edit added...

Dealing with months is somewhat tricky.

"How" do you want to add the months? Here are a few examples of ways to deal with months:

With a start date of 01/31/2024 and adding one month... simply incrementing the month will give you 02/31/2024, which is obviously not possible.

The average month length is 30.4375 days (factoring in leap years as well). This can be rounded down to 30 days. A start date of 01/31/2024 and adding 40 months (40 x 30 = 1200) will return 04/15/2027 as a result. Mathematically this makes sense, logically you would expect the result to land in May. I believe Smartsheet essentially does this in the background so it is the easiest to implement.

Going back to our first example (Starting on 01/31/2024 and adding one month), we could approach this a couple of different ways. We can say that any result that lands on an "impossible" day just gets "pulled back" to the last valid day of the month. So 01/31/2024 + 1 month would return 02/29/2024 (leap year).

The other option is to have it roll over to the first day of the following month. 01/31/2024 + 1 month = 03/01/2024.

How you would prefer to handle the result will dictate how to approach this.