# Custom Field Formula Help

• ### Question

• Hi,

I need to write a formula that prompts my PMO to chase activity owners in the run up to activity start but I cannot figure it out.....? My aim was to create a custom field with flag which links to baseline start to show the following;

If baseline start is greater than 14 days = green flag

If baseline start is between 7 - 14 days  = amber flag

If baseline start is less than 7 days = red flag

Thursday, September 10, 2015 12:01 PM

### All replies

• Liam,

You don't specify what exactly you are comparing the baseline start date to but I'll assume it is the current date. Also you don't specify if the day intervals are in working time or calendar time but since you use 7 and 14 as the criteria, I'll assume you mean calendar days.

Given those assumptions, the following formula in Text1 should give what you want:

IIf(DateDiff("d",[Current Date],[Baseline Start])>14,"3",IIf(datediff("d",[Current Date],[Baseline Start])<7,"1","2"))

The to set up the graphical indicators, the test should be "contains" 3, 2, or 1 with the appropriate color as the image.

Hope this helps.

John

Thursday, September 10, 2015 4:21 PM