Skip to main content
Solved

IF AND for Status Change

L
Forum|alt.badge.img+3

Hello, I'm trying to create a Formula that will change Status according to the fields "Start Date" & "End Date". 

If Today > End Date = "Delayed"

If Today < End Date = "In Progress"

If Today < Start Date = "Planning"

I have written the following formula that works for "Delayed", but shows "True" instead of "In Progress" or "Planning"

IF(TODAY() > {End Date},"Delayed", AND(IF(TODAY() < {End Date},"In Progress"),AND(IF(TODAY() < {Start Date},"Planning"))))
 
Anyway can identify my mistake? Thank you!!!

Best answer by Ben_Young1

Hey @lucas__mar! 

Give this a shot:

IF( {End Date}, IF( TODAY() > {End Date}, "Delayed", IF( TODAY() < {End Date}, "In Progress", IF( TODAY() < {Start Date}, "Planning" ) ) ), IF( {Start Date}, IF( TODAY() < {Start Date}, "Planning" ) ) )
View original
Did this topic help you find an answer to your question?

5 replies

Ben_Young1
Forum|alt.badge.img+20
  • Ben_Young1
  • Brainy
  • 520 replies
  • Answer
  • June 1, 2023

Hey @lucas__mar! 

Give this a shot:

IF( {End Date}, IF( TODAY() > {End Date}, "Delayed", IF( TODAY() < {End Date}, "In Progress", IF( TODAY() < {Start Date}, "Planning" ) ) ), IF( {Start Date}, IF( TODAY() < {Start Date}, "Planning" ) ) )
L
1 person likes this
  • L
    lucas__mar
L
Forum|alt.badge.img+3
  • lucas__mar
  • Author
  • Participating Frequently
  • 5 replies
  • June 1, 2023

This worked like a charm @Ben_Young1 
Thank you VERY VERY much 😃

L
Forum|alt.badge.img+3
  • lucas__mar
  • Author
  • Participating Frequently
  • 5 replies
  • June 2, 2023
Ben_Young1 wrote:

Hey @lucas__mar! 

Give this a shot:

IF( {End Date}, IF( TODAY() > {End Date}, "Delayed", IF( TODAY() < {End Date}, "In Progress", IF( TODAY() < {Start Date}, "Planning" ) ) ), IF( {Start Date}, IF( TODAY() < {Start Date}, "Planning" ) ) )

Hey @Ben_Young1 I'm trying to add a "Done" to that field, based on a value in an added single select field named "Completion" (Yes, No). The idea is that when the field is changed to "Yes", then the Status field changes to "Done". I tried with the IFs of your formula, but can't seem to get it right. Thxs for the help!

Ben_Young1
Forum|alt.badge.img+20
  • Ben_Young1
  • Brainy
  • 520 replies
  • June 2, 2023
lucas__mar wrote:

Hey @Ben_Young1 I'm trying to add a "Done" to that field, based on a value in an added single select field named "Completion" (Yes, No). The idea is that when the field is changed to "Yes", then the Status field changes to "Done". I tried with the IFs of your formula, but can't seem to get it right. Thxs for the help!


Hey @lucas__mar! 

Glad to see that the original formula worked as you'd hoped.
For the edit you're describing, give this a shot and let me know if it works or if something goes funky with it.
I haven't been able to test it yet, so I might have missed something:

IF( {Completion}, SWITCH( {Completion}, "Yes", "Done", "No", IF( {End Date}, IF( TODAY() > {End Date}, "Delayed", IF( TODAY() < {End Date}, "In Progress", IF( TODAY() < {Start Date}, "Planning", "" ) ) ), IF( {Start Date}, IF( TODAY() < {Start Date}, "Planning", "" ) ) ) ), IF( {End Date}, IF( TODAY() > {End Date}, "Delayed", IF( TODAY() < {End Date}, "In Progress", IF( TODAY() < {Start Date}, "Planning" ) ) ), IF( {Start Date}, IF( TODAY() < {Start Date}, "Planning" ) ) ) )

 

L
1 person likes this
  • L
    lucas__mar
L
Forum|alt.badge.img+3
  • lucas__mar
  • Author
  • Participating Frequently
  • 5 replies
  • June 3, 2023

Thank you again @Ben_Young1 this is perfect! I think after spending a few minutes I now also understand the logic behind. It's a good time to import that Formulas Base that's around and study them in depth hehe

Thank you!!!!

Reply


Most helpful members this week

Terms & Conditions