You need to sign in to do that
Don't have an account?
Sean 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!
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!
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
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
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","-"))))))))))
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","-"))))))))))