• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 694
  • Last Modified:

Need Help With Formula for Custom Text Field

I am trying to modify the code I found below to "flag" tasks that are coming due, say, next week, or 2 weeks from now...to make them easier for the owners to see.

The below code has over due and then RYG for Finish variance. I think I need something like the "Overdue" criteria that compares the Finish Date to the Date function to identify tasks coming due in certain time frames, but I can't get the syntax to make that happen
Switch([% Complete]=100,"Complete",(([% Complete]<100) And ([Finish]<Date())),"Overdue",(([Baseline Start]=ProjDateValue("NA")) Or ([Baseline Finish]=ProjDateValue("NA"))),"No BL",[Finish Variance]<=0,"Green",[Finish Variance]<=(ProjDateDiff([Project Start],[Project Finish])*0.1),"Yellow",[Finish Variance]>(ProjDateDiff([Project Start],[Project Finish])*0.1),"Red")

Open in new window

0
Rex85
Asked:
Rex85
  • 3
1 Solution
 
Rex85Author Commented:
Perhaps if I showed the code more clearly...


Switch( 


[% Complete]=100,"Complete",


(([% Complete]<100) And ([Finish]<Date())),"Overdue",


(([Baseline Start]=ProjDateValue("NA")) Or ([Baseline Finish]=ProjDateValue("NA"))),"No BL",


[Finish Variance]<=0,"Green",


[Finish Variance]<=(ProjDateDiff([Project Start],[Project Finish])*0.1),"Yellow",


[Finish Variance]>(ProjDateDiff([Project Start],[Project Finish])*0.1),"Red"


)

Open in new window

0
 
Rex85Author Commented:
I was able to work it out.

I used...

(([% Complete]<100) And ([Finish]>(Date()+18))),"Greater Than 2 Weeks")

The +18 is not perfect. I ran it on Tuesday, but the bin range 2 weeks, 1 week, etc... could shift if it was updated on a day besides tuesday.
0
 
Rex85Author Commented:
I got it to work.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now