Formula for custom field

[quinn87]

Hi Community, 

I am trying to create a formula to graphically represent my project's status but it's not going so well. Here is what i would like to formula to represent:

If task is 100% complete = Green
If task is not 100% and is passed the date it was due to finish = Red
If task is due in the next 14 days and is under 80% complete = amber
If task has not started = white

Do I also need to have a statement within the formula for other tasks. For example tasks that are in progress and on track and don't fall under the above statements?

If anyone has any suggestiong they would be greatly appreciated. 

[#2]

@quinn87 

We can certainly help but your description is missing some needed information.

1. Are you basing the "due to finish" on the scheduled finish date or the baseline finish date?

2. What defines the "next 14 days"? Is it from today's date or the status date?

3. For a "task not started", is that with respect to the scheduled start, baseline start, today's date or what?

It would be useful to see what you tried. The basic process for a multiple criteria formula is to examine each possibility in the proper order. For example, the first thing to test is tasks that are 100% complete. From there if a task may meet more than one criteria, check the most appropriate criteria first. For example, a task not yet started is also by default under 80% complete regardless of when it is/was due.

John

[#3]

@John-project 

Thanks for getting back to me so quickly. Your questions raised some good points, probably why my formula is not functioning correctly.

In response to your questions:

1. I would like to base the 'due to finish' on the scheduled finish date.

2. 'The next 14 days' should be in relation to the status date.

3. For a 'task not started' this should be in relation to the scheduled start date.

Here is the basis for the formula that I have been playing around with:

IIf([% Complete]=100,4,(IIf([Current Date]>=[Finish],3,(IIf([Current Date]>[Start] And [% Complete]=0,5,(IIf([Current Date]>[Finish]-14 And [% Complete]<80,2,(IIf([Current Date]<[Start],6,1)))))))))

Best,

Emma

[#4 · Accepted solution]

@quinn87 

Thanks for posting your formula. Here are my comments/suggestions.

First, you mention Status Date but your formula uses Current Date. They are different. I suspect you do mean the current date but if not, you need to change the formula appropriately. If you do change to the Status Date, make sure you have actually set a status date or the formula will give an error, testing for "NA" takes special consideration.

Second, the criteria for 14 days works okay the way you have implemented it as long as you mean 14 calendar days. However, if you really mean 14 working days, then you need to use the ProjDateDiff function and be sure to add an extra day (i.e. 480 minutes) if you want the difference to be inclusive (i.e. current date "through" the 14th working day). Without the extra day, the result will be "up to" the 14th day.

Third, your formula shows a result of "1" as the default but you don't say what color that is. I might assume it would be the same of for unstarted tasks (i.e. white) but maybe not.

Fourth, I'd move the test for tasks that don't start until after the current date as the second test in the formula. As currently structured a task with a start date within the 14 day window will show as amber, not white, but, tasks with a start date beyond the 14 day window will show a white.

Assuming you do want tests based on the current date and you do want 14 calendar days, this is how I'd write the formula:

IIf([% Complete]=100,4,(IIf([Current Date]<[Start],6,(IIf([Current Date]>=[Finish],3,(IIf([Current Date]>[Start] And [% Complete]=0,5,(IIf([Current Date]>[Finish]-14 And [% Complete]<80,2,1)))))))))

John

[#5]

Hi John,

That formula you corrected works a dream. Thanks so much.

Emma

[#6]

@quinn87 

You're welcome and thanks for the feedback.

Dreams I do, miracles are a little harder

John