Expand my Community achievements bar.

SOLVED

Calculated field that displays text

Avatar

Level 4

Hi All, 

 

I'm trying to create a calculated field on a document form that displays the following text when the number present falls into one of the following 3 ranges:

 

< 0 = "Late"

=0 = "On Time"

>0 = "Early"

The field will pull the numerical data from another custom field on the form.

1 Accepted Solution

Avatar

Correct answer by
Level 5

similar concept i did for a client used the following formula...

 

IF({status}="PLD","1. Trending Early",IF({actualCompletionDate} = "",IF(WEEKDAYDIFF({DE:Final Approved Deliverable Due Date}, {projectedCompletionDate})<0,"1. Trending Early",IF(WEEKDAYDIFF({DE:Final Approved Deliverable Due Date}, {projectedCompletionDate})<0,"3. Trending Behind","5. Trending Late")),IF(WEEKDAYDIFF({DE:Final Approved Deliverable Due Date}, {actualCompletionDate})<0,"2. Early",IF(WEEKDAYDIFF({plannedCompletionDate}, {actualCompletionDate}) <0,"4. Behind","6. Late"))))

View solution in original post

4 Replies

Avatar

Correct answer by
Level 5

similar concept i did for a client used the following formula...

 

IF({status}="PLD","1. Trending Early",IF({actualCompletionDate} = "",IF(WEEKDAYDIFF({DE:Final Approved Deliverable Due Date}, {projectedCompletionDate})<0,"1. Trending Early",IF(WEEKDAYDIFF({DE:Final Approved Deliverable Due Date}, {projectedCompletionDate})<0,"3. Trending Behind","5. Trending Late")),IF(WEEKDAYDIFF({DE:Final Approved Deliverable Due Date}, {actualCompletionDate})<0,"2. Early",IF(WEEKDAYDIFF({plannedCompletionDate}, {actualCompletionDate}) <0,"4. Behind","6. Late"))))

Avatar

Level 4

Thanks Jarrett!  Is your formula tied to a task/project status?  How would I go about tying it to a custom field?

Avatar

Level 5

you would replace the status in the { } with the custom field {DE:Custom Field Name}

 

IF({DE:Custom Field Name} < 0,"Late",IF({DE: Custom Field Name} = 0, "On Time","Early"))

Avatar

Level 4

Thanks for all your help with this Graham! I did have one last question regarding this formula. If you were trying to capture scores that fall between two variables, how would you capture that in this formula?  >1 but <4 ?