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 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

Who is Participating?
Rex85Connect With a Mentor Author 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.
Rex85Author Commented:
Perhaps if I showed the code more clearly...


[% 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

Rex85Author Commented:
I got it to work.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.