Generating text in a cell based on a date in another cell, with conditions

Jeni B

Hello!

I'm new to smartsheets and I'm sure I've just made some kind of simple error or overlooked something.

I am trying to create a formula that will generate text in a cell [Recurrence], "1 week", "2 weeks", or "4 weeks" based on how far out today is from a contract start date [Start Date] - either less than 6 months, 6months to 12 months, or more than 12 months. I thought the following would work, but it doesn't. Can anyone help me with this?

=IF([Start Date]@row >= TODAY()-180, “1 week”, IF(AND([Start Date]@row < TODAY()-180, [Start Date]@row >= TODAY()-365), “2 weeks”, IF([Start Date]@row > TODAY()-365, “4 weeks”, “”)))

I also tried the following, but it didn't work either

=IF([Start Date]@row <= TODAY(-365), “4 weeks”, IF(AND([Start Date]@row <= TODAY(-180), [Start Date]@row > TODAY(-365)), “2 weeks”, IF( [Start Date]@row > TODAY(-180), “1 week”)))

Mike TV

@Jeni B

I was getting the same #UNPARSEABLE when I copy-paste your formula into a test sheet. However, I noticed that the [Start Date]@row wasn't appearing in color. When it appears in color you know it's found the location you're referencing. I had to start typing it from scratch and the [Start Date]@row then changed to colors like it was supposed to and it worked. I didn't understand the results it was spitting out based on what your post said but that should at least get you past the #UNPARSEABLE errors.

Paul Newcome

The issue is the quotes. See how they are slanted above to show open vs closed whereas "these quotes" are straight up and down? The slanted ones are not recognized as valid characters by Smartsheet. Try retyping the formula directly in the sheet, here in the community forum, or in a text editor such as Notepad. Programs such as Word will always generate those types of quotes and as such are not the best option for creating/editing formulas.

Edit: That's why the third formula works when the other two did not. It contains the proper type of quotes.

Mike TV

@Jeni B

What problem did you have with the bottom formula in your post? It looks to be formatted correctly as long as the results are what you are after.

Jeni B

@Mike TV I get #UNPARSEABLE with both of those formulas

I've triple checked that the Start Date column is formatted as date only. I think the problem is the 6-12 month/"2 weeks" part of the formula. When I run the following, there are no problems, except it obviously gives me "2 weeks" as the output when the date is more than 12 months back and I need it to change to "4 weeks" once the start date is older than 12 months.

=IF([Start Date]@row < TODAY(-180), "2 weeks", IF([Start Date]@row >= TODAY(-180), "1 week", ""))

Mike TV

@Jeni B

I was getting the same #UNPARSEABLE when I copy-paste your formula into a test sheet. However, I noticed that the [Start Date]@row wasn't appearing in color. When it appears in color you know it's found the location you're referencing. I had to start typing it from scratch and the [Start Date]@row then changed to colors like it was supposed to and it worked. I didn't understand the results it was spitting out based on what your post said but that should at least get you past the #UNPARSEABLE errors.

Paul Newcome

The issue is the quotes. See how they are slanted above to show open vs closed whereas "these quotes" are straight up and down? The slanted ones are not recognized as valid characters by Smartsheet. Try retyping the formula directly in the sheet, here in the community forum, or in a text editor such as Notepad. Programs such as Word will always generate those types of quotes and as such are not the best option for creating/editing formulas.

Edit: That's why the third formula works when the other two did not. It contains the proper type of quotes.

Mike TV

@Paul Newcome

Thanks Paul. I always miss those slanted quotes. I was wondering why it needed me to retype the same thing.

Jeni B

@Mike TV @Paul Newcome

Thank you both!! Lesson learned to NOT type out my formulas in Word . Works perfectly when I type it directly into SmartSheet.

Paul Newcome

Happy to help. 👍️