Forum Discussion
Formula for custom field
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
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
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
- Hi John,
That formula you corrected works a dream. Thanks so much.
Emma