Skip to main content
Solved

Help with custom formula

  • January 22, 2021
  • 4 replies
  • 31 views
H

I tried to make a custom formula with this expression, but it fails.

If(Or(Phase= “5. On Hold”, StartDate>Today()),
“5. On Hold”,
If(Phase= “Complete”,
“Complete”,
If(Or(Phase= “1. ToDo”,
And(DueDate<=(Today()+7), Phase <>“Complete”, Phase<>“5. On Hold” ) ),
“1. ToDo”,
If(Phase = “2. Planning”,
“2. Planning”,
If(Phase = “3. Doing”,
“3. Doing”,
If(Phase = “4. Controlling”,
“4. Controlling”
))))))

Do you have any advice?

Best answer by Hans-Christian_

Replaced “ with "

If(Or(Phase = “5. On Hold”, StartDate > Today()),
“5. On Hold”,
If(Phase = “Complete”,
“Complete”,
If(Or(Phase = “1. ToDo”,
And(DueDate <= Today(), Phase <> “Complete”, Phase <> “5. On Hold” ) ),
“1. ToDo”,
If(Phase = “2. Planning”,
“2. Planning”,
If(Phase = “3. Doing”,
“3. Doing”,
If(Phase = “4. Controlling”,
“4. Controlling”
))))))

…but still does not work.


Figuered:

IF(OR(Phase = “5. On Hold”, StartDate > TODAY()),
“5. On Hold”,
IF(Phase = “Complete”,
“Complete”,
IF(OR(Phase = “1. ToDo”,
AND(DueDate <= TODAY(), Phase != “Complete”, Phase != “5. On Hold” ) ),
“1. ToDo”,
IF(Phase = “2. Planning”,
“2. Planning”,
IF(Phase = “3. Doing”,
“3. Doing”,
IF(Phase = “4. Controlling”,
“4. Controlling”
))))))

4 replies

Databaser
Forum|alt.badge.img+25
  • Databaser
  • Brainy
  • January 22, 2021

Hi @Hans-Christian_Franc and welcome to the community!

I think the “+7” part after Today() isn’t possible. If you remove that, does it work?

Jan - Databaser
H
  • Hans-Christian_
  • Author
  • New Participant
  • January 22, 2021

Hi @Hans-Christian_Franc and welcome to the community!

I think the “+7” part after Today() isn’t possible. If you remove that, does it work?


@Databaser - nope, no change

H
  • Hans-Christian_
  • Author
  • New Participant
  • January 22, 2021

Replaced “ with "

If(Or(Phase = “5. On Hold”, StartDate > Today()),
“5. On Hold”,
If(Phase = “Complete”,
“Complete”,
If(Or(Phase = “1. ToDo”,
And(DueDate <= Today(), Phase <> “Complete”, Phase <> “5. On Hold” ) ),
“1. ToDo”,
If(Phase = “2. Planning”,
“2. Planning”,
If(Phase = “3. Doing”,
“3. Doing”,
If(Phase = “4. Controlling”,
“4. Controlling”
))))))

…but still does not work.

H
  • Hans-Christian_
  • Author
  • New Participant
  • Answer
  • January 22, 2021

Replaced “ with "

If(Or(Phase = “5. On Hold”, StartDate > Today()),
“5. On Hold”,
If(Phase = “Complete”,
“Complete”,
If(Or(Phase = “1. ToDo”,
And(DueDate <= Today(), Phase <> “Complete”, Phase <> “5. On Hold” ) ),
“1. ToDo”,
If(Phase = “2. Planning”,
“2. Planning”,
If(Phase = “3. Doing”,
“3. Doing”,
If(Phase = “4. Controlling”,
“4. Controlling”
))))))

…but still does not work.


Figuered:

IF(OR(Phase = “5. On Hold”, StartDate > TODAY()),
“5. On Hold”,
IF(Phase = “Complete”,
“Complete”,
IF(OR(Phase = “1. ToDo”,
AND(DueDate <= TODAY(), Phase != “Complete”, Phase != “5. On Hold” ) ),
“1. ToDo”,
IF(Phase = “2. Planning”,
“2. Planning”,
IF(Phase = “3. Doing”,
“3. Doing”,
IF(Phase = “4. Controlling”,
“4. Controlling”
))))))

Databaser
Terms & ConditionsAccessibility statement