+ Start a Discussion
Sean GilmartinSean Gilmartin 

Custom Contact Field Question

Hi all, 

I would like to create a formula for a Custom Contact Field which would rate a contact by how much time has passed since the Date of their Last Gift. For example, if they have given a gift in the last 3 months from today, they get an A. If they have given between 3 and 6 months ago, a B. Between 6 and 9 months ago a C and between 9 and 12 months ago a D. For the first question, I thought to create a simple IF statement such as this: IF(TODAY()- causeview__Date_of_Last_Gift__c <= 90,"A","-") to get the proper rating and I figure I can create similar IF statements to get the other ratings but I do not know how to put successive IF statements together under one field formula. If anybody could help with this, I would appreciate it. Thanks!
Best Answer chosen by Sean Gilmartin
Sean GilmartinSean Gilmartin
This formula was the one that ended up working. Question answered.
  
IF(OR(TODAY()- causeview__Date_of_Last_Gift__c <=90),"A", IF(OR(TODAY()- causeview__Date_of_Last_Gift__c <=180),"B", IF(OR(TODAY()- causeview__Date_of_Last_Gift__c <=270),"C", IF(OR(TODAY()- causeview__Date_of_Last_Gift__c <=360),"D", IF(OR(TODAY()-causeview__Date_of_Last_Gift__c <=540),"E", IF(OR(TODAY()- causeview__Date_of_Last_Gift__c <=720),"F", IF(OR(TODAY()- causeview__Date_of_Last_Gift__c <=1080),"G", IF(OR(TODAY()- causeview__Date_of_Last_Gift__c <=1440),"H", IF(OR(TODAY()- causeview__Date_of_Last_Gift__c <=1800),"I", IF(OR(TODAY()- causeview__Date_of_Last_Gift__c >1800),"J","-"))))))))))

All Answers

Sean GilmartinSean Gilmartin
I was able to create a series of IF Statements successfully connected together (syntax checks out) as such: 

IF(TODAY()- causeview__Date_of_Last_Gift__c <= 90,"A", IF(OR(TODAY()- causeview__Date_of_Last_Gift__c >=91, TODAY()- causeview__Date_of_Last_Gift__c <=180),"B",IF(OR(TODAY()- causeview__Date_of_Last_Gift__c >=181,TODAY()- causeview__Date_of_Last_Gift__c <=270),"C", IF(OR(TODAY()- causeview__Date_of_Last_Gift__c >=271,TODAY()- causeview__Date_of_Last_Gift__c <=360),"D", IF(OR(TODAY()-causeview__Date_of_Last_Gift__c <=361,TODAY()- causeview__Date_of_Last_Gift__c >=540),"E", IF(OR(TODAY()- causeview__Date_of_Last_Gift__c >=541,TODAY()- causeview__Date_of_Last_Gift__c <=720),"F", IF(OR(TODAY()- causeview__Date_of_Last_Gift__c >=721,TODAY()- causeview__Date_of_Last_Gift__c <=1080),"G", IF(OR(TODAY()- causeview__Date_of_Last_Gift__c >=1081,TODAY()- causeview__Date_of_Last_Gift__c <=1440),"H", IF(OR(TODAY()- causeview__Date_of_Last_Gift__c >=1441,TODAY()- causeview__Date_of_Last_Gift__c <=1800),"I","J")))))))))

However, it is only showing the people who have donated in under 90 days as A, and everybody else as B if they gave above 90 days ago in general or J if they never gave. My goal is below:

Time between Today and Last Gift in Months, Or Days, And the Rating for that Donor
0-3 months, 0-90 days, A
3-6 months, 91-180 days, B
6-9 months, 181-270 days, C
9-12 months, 271-360 days, D
12-18 months, 361-540 days, E
18-24 months, 541-720 days, F
24-36 months, 721-1080 days, G
36-48 months, 1081-1440 days, H
48-60 months, 1441-1800 days, I
60+months, 1801 days+, J

Sean GilmartinSean Gilmartin
The incorrect signs of <=361 and >=540 have been reversed. But the problem is still evident. 
Sean GilmartinSean Gilmartin
I also tried the formula like this but no better luck with it:

IF(TODAY()- causeview__Date_of_Last_Gift__c <= 90,"A", IF(OR(TODAY()- causeview__Date_of_Last_Gift__c >=91, TODAY()- causeview__Date_of_Last_Gift__c <=180),"B",IF(OR(TODAY()- causeview__Date_of_Last_Gift__c >=181, TODAY()- causeview__Date_of_Last_Gift__c <=270),"C", IF(OR(TODAY()- causeview__Date_of_Last_Gift__c >=271, TODAY()- causeview__Date_of_Last_Gift__c <=360),"D", IF(OR(TODAY()-causeview__Date_of_Last_Gift__c >=361, TODAY()- causeview__Date_of_Last_Gift__c <=540),"E", IF(OR(TODAY()- causeview__Date_of_Last_Gift__c >=541, TODAY()- causeview__Date_of_Last_Gift__c <=720),"F", IF(OR(TODAY()- causeview__Date_of_Last_Gift__c >=721, TODAY()- causeview__Date_of_Last_Gift__c <=1080),"G", IF(OR(TODAY()- causeview__Date_of_Last_Gift__c >=1081, TODAY()- causeview__Date_of_Last_Gift__c <=1440),"H", IF(OR(TODAY()- causeview__Date_of_Last_Gift__c >=1441, TODAY()- causeview__Date_of_Last_Gift__c <=1800),"I", IF(TODAY()- causeview__Date_of_Last_Gift__c >=1801,"J","-"))))))))))
Sean GilmartinSean Gilmartin
This formula was the one that ended up working. Question answered.
  
IF(OR(TODAY()- causeview__Date_of_Last_Gift__c <=90),"A", IF(OR(TODAY()- causeview__Date_of_Last_Gift__c <=180),"B", IF(OR(TODAY()- causeview__Date_of_Last_Gift__c <=270),"C", IF(OR(TODAY()- causeview__Date_of_Last_Gift__c <=360),"D", IF(OR(TODAY()-causeview__Date_of_Last_Gift__c <=540),"E", IF(OR(TODAY()- causeview__Date_of_Last_Gift__c <=720),"F", IF(OR(TODAY()- causeview__Date_of_Last_Gift__c <=1080),"G", IF(OR(TODAY()- causeview__Date_of_Last_Gift__c <=1440),"H", IF(OR(TODAY()- causeview__Date_of_Last_Gift__c <=1800),"I", IF(OR(TODAY()- causeview__Date_of_Last_Gift__c >1800),"J","-"))))))))))
This was selected as the best answer