Topic Labels: Formulas
657 2
cancel
Showing results for
Did you mean:
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.'

``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")))))))))))))))))))))))``