Help

IF Formula Error: Please help!

Topic Labels: Formulas
382 2
cancel
Showing results for 
Search instead for 
Did you mean: 
brinorberg
4 - Data Explorer
4 - Data Explorer

Inside my client management system, I have three columns:

  1. Client status - Active or completed [this changes automatically when client reaches a certain amount of weeks]
  2. Weeks - How many weeks they have been inside the program [this has a formula]
  3. Start date - when they began the program

My program was 4 months and then I extended it to 6 months.

Here is the formula inside the column 'Weeks' that works for 4 months:

 

IF({Days Since Joined}<7,"Week 1", IF({Days Since Joined}<14,"Week 2", IF({Days Since Joined}<21,"Week 3", IF({Days Since Joined}<28,"Week 4", IF({Days Since Joined}<35,"Week 5", IF({Days Since Joined}<42,"Week 6", IF({Days Since Joined}<49,"Week 7", IF({Days Since Joined}<56,"Week 8", IF({Days Since Joined}<63,"Week 9", IF({Days Since Joined}<70,"Week 10", IF({Days Since Joined}<77,"Week 11", IF({Days Since Joined}<84,"Week 12", IF({Days Since Joined}<91,"Week 13", IF({Days Since Joined}<98,"Week 14", IF({Days Since Joined}<105,"Week 15", IF({Days Since Joined}<112,"Week 16", IF({Days Since Joined}>=119,"Completed")))))))))))))))))
 
My assumption is that I just add week 17-week 23 and end it with --> IF({Days Since Joined}>168,"Completed")
 
Here is the new formula for the 6 months (24 weeks):

IF({Days Since Joined}<7,"Week 1", IF({Days Since Joined}<14,"Week 2", IF({Days Since Joined}<21,"Week 3", IF({Days Since Joined}<28,"Week 4", IF({Days Since Joined}<35,"Week 5", IF({Days Since Joined}<42,"Week 6", IF({Days Since Joined}<49,"Week 7", IF({Days Since Joined}<56,"Week 8", IF({Days Since Joined}<63,"Week 9", IF({Days Since Joined}<70,"Week 10", IF({Days Since Joined}<77,"Week 11", IF({Days Since Joined}<84,"Week 12", IF({Days Since Joined}<91,"Week 13", IF({Days Since Joined}<98,"Week 14", IF({Days Since Joined}<105,"Week 15", IF({Days Since Joined}<112,"Week 16", IF({Days Since Joined}<119,"Week 17", IF({Days Since Joined}<126,"Week 18", IF({Days Since Joined}<133,"Week 19", IF({Days Since Joined}<140,"Week 20", IF({Days Since Joined}<147,"Week 21", IF({Days Since Joined}<154,"Week 22", IF({Days Since Joined}<161,"Week 23", IF({Days Since Joined}>168,"Completed") 

My assumption was wrong and I am getting the error: 'Sorry, there was a problem saving this field. Invalid formula. Please check your formula text.'

Please help! 

 
2 Replies 2
brinorberg
4 - Data Explorer
4 - Data Explorer

I also tried including all of the parentheses at the end: "Completed")))))))))))))))))

joshsorenson
6 - Interface Innovator
6 - Interface Innovator

Try this:

IF({Days Since Joined}<7,"Week 1", IF({Days Since Joined}<14,"Week 2", IF({Days Since Joined}<21,"Week 3", IF({Days Since Joined}<28,"Week 4", IF({Days Since Joined}<35,"Week 5", IF({Days Since Joined}<42,"Week 6", IF({Days Since Joined}<49,"Week 7", IF({Days Since Joined}<56,"Week 8", IF({Days Since Joined}<63,"Week 9", IF({Days Since Joined}<70,"Week 10", IF({Days Since Joined}<77,"Week 11", IF({Days Since Joined}<84,"Week 12", IF({Days Since Joined}<91,"Week 13", IF({Days Since Joined}<98,"Week 14", IF({Days Since Joined}<105,"Week 15", IF({Days Since Joined}<112,"Week 16", IF({Days Since Joined}<119,"Week 17", IF({Days Since Joined}<126,"Week 18", IF({Days Since Joined}<133,"Week 19", IF({Days Since Joined}<140,"Week 20", IF({Days Since Joined}<147,"Week 21", IF({Days Since Joined}<154,"Week 22", IF({Days Since Joined}<161,"Week 23", IF({Days Since Joined}<168,"Week 24", IF({Days Since Joined}>=168,"Completed")))))))))))))))))))))))