Need Help With Formula for Custom Text Field

Posted on 2011-10-28
Last Modified: 2012-05-12
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

Question by:Rex85

    Author Comment

    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


    Accepted Solution

    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.

    Author Closing Comment

    I got it to work.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Suggested Solutions

    The technology coordinator knew that implementing a help desk call-tracking system could increase teacher satisfaction and cut costs. He bought tracking software and had it installed and configured. He modified the telephone routing system and hired…
    If you are using Scrum Framework or another agile process, a retrospective may be part of it. Does your team perform retrospectives? Are you getting value from your retrospectives? I see a common anti-pattern when people conduct a retrospective f…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now