Date Formula dependent on text

Zoe Chrysopoulos

Hi All,

 

I am looking for a formula that can ideftify text in a column and convert it to dates.

 

More specifically I need it to pick up a cell says "X" convert to today's date +30 days, or if it says "Y" convert to today's date +30?

 

I have found a formula that works in identifying only one set of text:

=IF([Project Name]34 = "Form 1", TODAY() + 30)

 

But I am having troubling expaning on this:

=IF([Project Name]34 = "Form 1", TODAY() + 30), IF([Project Name]34 = "Form 2", TODAY() + 60)  etc

This formula in an incorrect argument.

 

Any ideas?

 

Thanks for your time and help! Very much appreciated!

Zoe Chrysopoulos

I've worked it out!!

 

For anyone who needs it:

IF([Project Name]39 = "Form 1", TODAY() + 30) + IF([Project Name]39 = "Form 2", TODAY() + 60) + IF([Project Name]39 = "Form 3", TODAY() + 90)

Mike Wilday

Hi Zoe, I am looking at your formula and It doesn't appear correct. You are Your first formula looked accurate but with some minor adjustments. 

 

=IF([Project Name]34 = "Form 1", TODAY() + 30, IF([Project Name]34 = "Form 2", TODAY() + 60)) 

 

You were closing the first if statement too early, all of the closing parenthesis need to be at the end of the massive if statement. 

 

Upon testing your solution, I get a #invalid operation error when the Project Name says "Form 2"

Zoe Chrysopoulos

Hi Mike, 

 

Yes when the project Name says' "Form 2" I get #invalid operation error also. To overcome this I put a dummy Project name as it seemed to be a n issue with the second command.

 

I tested you formula and it does work apart for the same proble with the "Form 2" Issue.

 

Not sure why it does recognise the second command.

J. Craig Williams

Zoe / Mike,

 

I do not get #INVALID OPERATION when using this formula:

 

=IF([Project Name]34 = "Form 1", TODAY() + 30, IF([Project Name]34 = "Form 2", TODAY() + 60)) 

 

Is this the one you are using?

 

Craig

 

Zoe Chrysopoulos

Thanks Craig, your formula seems to be worked a treat! Maybe I had to many parenthesis in mine like Mike mentioned

 

Not sure

Mike Wilday

Glad it was figured out. The issue was your parenthesis were not placed correctly. You can't close the IF statement until after all the arguments are passed. 

 

You're original formula closed the if statement after the +30 ::

=IF([Project Name]34 = "Form 1", TODAY() + 30)<----Closed if statement -- shouldn't be there, move to end-->, IF([Project Name]34 = "Form 2", TODAY() + 60)

If you're going to add additional arguments to the IF statement, leave off the last parenthesis and put it at the end of the statement. If([location]1="Form 1", Today()+30, IF statement, argument, IF statement, argument ))) Close off each open parenthesis at the end. 

Zoe Chrysopoulos

That makes sense! Thank your for your advice Mike! I can use that piece of information for my next formulas also!

 

Mike Wilday

Definitely. Those if statements can be tricky, I use some developer tools like notepad++ or some other code editor to make sure I have my parenthesis in in the right order and not too many.

 

I also keep Smartsheet's error page bookmarked for quick reference.
https://help.smartsheet.com/articles/2476176-formula-error-messages