Ask Search:
Angela MorroneAngela Morrone 

Complex Date Field Calculation

Hello
I have to import data into existing contact records.
It comes from an online form.
There are two pieces of available data:
1.) Date stamp of when form was submitted
2.) "Current Grade" (this is any grade from Pre-K through 12th grade and it is based on the person's grade at the time the form was submitted.


In Excel, I have calculated a third piece of information but I can't figure out how it could help me in Salesforce. It is "Projected Graduation Year".

What I need Salesforce to do is this:
1.) Report the contact's 'current grade' in real time.  So I will have data from two years ago when a contact was in 2nd grade.  But when I open their record, it will show they are in 4th grade.  A new grade year will begin in June of each year.
2.) Calculate the contact's 'projected year of graduation'.  It is assumed that each contact will graduate in May of their 12th year.

The forumula I used in Excel was:     =W2+(12-V2)
Where W2 is the year it was (in May) when the contact submitted the form)
Where V2 is the grade the contact was when the contact submitted the information.
 
Best Answer chosen by Angela Morrone
Angela MorroneAngela Morrone
I'm going to close this out, but I'll be back because this is only the first part of what I'm needing to do.
I will still need to figure out the projected graduation year and then make it all work in the process buidler as Tom has recommended.
Here is the code I ended up using: 

IF(Today()<Date(Year(Today()),6,1),
Case(Grade_When_Submitted__c,
'12th',1,
'11th',2,
'10th',3,
'9th',4,
'8th',5,
'7th',6,
'6th',7,
'5th',8,
'4th',9,
'3rd',10,
'2nd',11,
'1st',12,
'Kindergarten',13,
'Pre-K',14,
Null),
Case(Grade_When_Submitted__c,
'12th',13,
'11th',12,
'10th',11,
'9th',10,
'8th',9,
'7th',8,
'6th',7,
'5th',6,
'4th',5,
'3rd',4,
'2nd',3,
'1st',2,
'Kindergarten',1,
'Pre-K',0,
Null))

All Answers

Steve MolisSteve Molis
I've done Formulas like that, let me dig around my Dev org and see if I still have one
Angela MorroneAngela Morrone
Thank you, Steve!
Steve MolisSteve Molis
Okay, it might take a while to track it down.  When do you need it by?  I'm on the East Coast (Boston) 
Angela MorroneAngela Morrone
It's not urgent.  But I am actively trying to figure it out.  Thanks
Tom HoffmanTom Hoffman
Steve...I'm watching Live PD & having a DFH 120...so this looked like my Friday night equivalent of a crossword puzzle.  Feel free to correct my thoughts below...but I think this is how I would tackle this. 

We need to use formulas to determine three values,:
Years to Go (years between today * graduation date)
Graduation Year (as a number)
Actual Current Grade 

The only information we have is 'Current Grade' at the time the form is completed
Date the form is completed 

Based on the above, here is my thought:

Years to Go (number) – use this field to store the the years remaining to graduation when the form is created.  This assumes the current grade you are capturing is reported as text field.  Set this value with a workflow or PB when a record is created
 
IF(Today()<Date(Year(Today(),6,1),
Case(Current Grade field api name,
‘12th’, 0,
‘11th’, 1,
‘10th’, 2,
‘9th’, 3,
‘8th’, 4,
‘7th’, 5,
‘6th’, 6,
‘5th’, 7,
‘4th’, 8,
‘3rd’, 9,
‘2nd’, 10,
‘1st’, 11,
‘Kindergarten’,12,
‘Pre-K’, 13,
Null),
Case(Current Grade field api name,
‘12th’, 1,
‘11th’, 2,
‘10th’, 3,
‘9th’, 4,
‘8th’, 5,
‘7th’, 6,
‘6th’, 7,
‘5th’, 8,
‘4th’, 9,
‘3rd’, 10,
‘2nd’, 11,
‘1st’, 12,
‘Kindergarten’,13,
‘Pre-K’, 14,
Null))
 
This will return a number field between 0-14, 0-13 prior to June 1st, 1-14 for after June 1
st.
 
Now to calculate Projected Graduation Year & display w/o a comma after 2 (ex 2020 vs 2,020):
Text(Year(DateValue(CreatedDate))+YearsToGo)
 
For example – if you select Pre-K today in July 2018, the projected graduation year would 2032.  If you selected pre-k on April 30, the projected graduation year would be 2031.
 
Annnnddd one last field, ‘Grade Today’ to find the real ‘current grade’ not the one you are capturing when the form is being created.
 
We’ll use the non Text() vers of the Projected Graduation Year to determine the student’s grade today.
 
IF(Today()<Date(Year(Today(),6,1),
Case((Year(DateValue(CreatedDate))+YearsToGo) -Year(Today()),
0, ‘12th’,
1, ‘11th’,
2, ‘10th’,
3, ‘9th’,
4, ‘8th’,
5, ‘7th’,
6, ‘6th’,
7, ‘5th’,
8, ‘4th’,
9, ‘3rd’,
10, ‘2nd’,
11, ‘1st’,
12, ‘Kindergarten’,
13, ‘Pre-K’,
null),
Case((Year(DateValue(CreatedDate))+YearsToGo) -Year(Today()),
0,’Graduated’,
1, ‘12th’,
2, ‘11th’,
3, ‘10th’,
4, ‘9th’,
5, ‘8th’,
6, ‘7th’,
7, ‘6th’,
8, ‘5th’,
9, ‘4th’,
10, ‘3rd,’
11, ‘2nd’,
12, ‘1st’,
13, ‘Kindergarten’,
14, ‘Pre-K’,
null))

 
Angela MorroneAngela Morrone
Hi Tom,
Thank you for taking the time to play with this.
I got a syntax error with the following code to first find out 'years to go'

IF(Today()<Date(Year(Today(),6,1),
Case(Grade_When_Submitted__c,
‘12th’, 0,
‘11th’, 1,
‘10th’, 2,
‘9th’, 3,
‘8th’, 4,
‘7th’, 5,
‘6th’, 6,
‘5th’, 7,
‘4th’, 8,
‘3rd’, 9,
‘2nd’, 10,
‘1st’, 11,
‘Kindergarten’,12,
‘Pre-K’, 13,
Null),
Case(Grade_When_Submitted__c,
‘12th’, 1,
‘11th’, 2,
‘10th’, 3,
‘9th’, 4,
‘8th’, 5,
‘7th’, 6,
‘6th’, 7,
‘5th’, 8,
‘4th’, 9,
‘3rd’, 10,
‘2nd’, 11,
‘1st’, 12,
‘Kindergarten’,13,
‘Pre-K’, 14,
Null))
Tom HoffmanTom Hoffman
It was missing a ) to close the Year() around Today()

Also, when copying & pasting, sometimes SF doesn't like the ' ' and sometimes carriage returns, but otherwise, I believe this syntax is correct.  If using PB you'll need to add the object name like this: [ObjectName].Grade_When_Submitted__c

IF(Today()<Date(Year(Today()),6,1),
Case(Grade_When_Submitted__c,
‘12th’,0,
‘11th’,1,
‘10th’,2,
‘9th’,3,
‘8th’,4,
‘7th’,5,
‘6th’,6,
‘5th’,7,
‘4th’,8,
‘3rd’,9,
‘2nd’,10,
‘1st’,11,
‘Kindergarten’,12,
‘Pre-K’,13,
Null),
Case(Grade_When_Submitted__c,
‘12th’,1,
‘11th’,2,
‘10th’,3,
‘9th’,4,
‘8th’,5,
‘7th’,6,
‘6th’,7,
‘5th’,8,
‘4th’,9,
‘3rd’,10,
‘2nd’,11,
‘1st’,12,
‘Kindergarten’,13,
‘Pre-K’,14,
Null))
Angela MorroneAngela Morrone
I'm sorry - I don't know what PB means
Tom HoffmanTom Hoffman
Peanut Butter or Process Builder, depends on where its used really ;)

Your PB would be on the object being created, evaluate the process builder when a record is created only, only criteria would be 'current grade' is null boolean false, immediate action - update records, update the record that caused the process to be evaluated, and then your field update would be this formula. 

Your YearsToGo field (number field) would be set by formula, then enter this for the formula

IF(Today()<Date(Year(Today()),6,1),
Case([ObjectName].Grade_When_Submitted_FieldAPIName__c,
‘12th’,0,
‘11th’,1,
‘10th’,2,
‘9th’,3,
‘8th’,4,
‘7th’,5,
‘6th’,6,
‘5th’,7,
‘4th’,8,
‘3rd’,9,
‘2nd’,10,
‘1st’,11,
‘Kindergarten’,12,
‘Pre-K’,13,
Null),
Case([ObjectName].Grade_When_Submitted_FieldAPIName__c,
‘12th’,1,
‘11th’,2,
‘10th’,3,
‘9th’,4,
‘8th’,5,
‘7th’,6,
‘6th’,7,
‘5th’,8,
‘4th’,9,
‘3rd’,10,
‘2nd’,11,
‘1st’,12,
‘Kindergarten’,13,
‘Pre-K’,14,
Null))
 
Angela MorroneAngela Morrone
ahh.. got it.
I was just trying to create the first formula via a custom field.
Since all of my contacts are already imported and I have to import their current grade, it felt like process builder was going to be a bit over my head.  But it might be unavoidable?
Tom HoffmanTom Hoffman
It is and it is not. If you wanted to set years to go in excel and update, that would work, but you would likely need a pb for future records. It's not that we can't create 3 appropriate formulas, it's the third one probably will be over 5000 character compile limit, so I'm being preemptive with a process flow.
Angela MorroneAngela Morrone
FYI - playing around with tit - I'm still getting a syntax error.. 
Angela MorroneAngela Morrone
Ok, I found the error but now new error Error:

"Formula result is data type (Number), incompatible with expected data type (Text)."
Angela MorroneAngela Morrone
I set the field data type to number b/c the system is asking for it.
The calculation is slightly off (see picture).
Here is my current code for that field:
IF(Today()<Date(Year(Today()),6,1), 
Case(Grade_When_Submitted__c, 
'12th',0, 
'11th',1, 
'10th',2, 
'9th',3, 
'8th',4, 
'7th',5, 
'6th',6, 
'5th',7, 
'4th',8, 
'3rd',9, 
'2nd',10, 
'1st',11, 
'Kindergarten',12, 
'Pre-K',13, 
Null), 
Case(Grade_When_Submitted__c, 
'12th',1, 
'11th',2, 
'10th',3, 
'9th',4, 
'8th',5, 
'7th',6, 
'6th',7, 
'5th',8, 
'4th',9, 
'3rd',10, 
'2nd',11, 
'1st',12, 
'Kindergarten',13, 
'Pre-K',14, 
Null))​



User-added image
Angela MorroneAngela Morrone
I have a feeling the count down is backwards.. I'll give it a go.
Angela MorroneAngela Morrone
I'm going to close this out, but I'll be back because this is only the first part of what I'm needing to do.
I will still need to figure out the projected graduation year and then make it all work in the process buidler as Tom has recommended.
Here is the code I ended up using: 

IF(Today()<Date(Year(Today()),6,1),
Case(Grade_When_Submitted__c,
'12th',1,
'11th',2,
'10th',3,
'9th',4,
'8th',5,
'7th',6,
'6th',7,
'5th',8,
'4th',9,
'3rd',10,
'2nd',11,
'1st',12,
'Kindergarten',13,
'Pre-K',14,
Null),
Case(Grade_When_Submitted__c,
'12th',13,
'11th',12,
'10th',11,
'9th',10,
'8th',9,
'7th',8,
'6th',7,
'5th',6,
'4th',5,
'3rd',4,
'2nd',3,
'1st',2,
'Kindergarten',1,
'Pre-K',0,
Null))
This was selected as the best answer
Tom HoffmanTom Hoffman
Hey sorry, just seeing all the messages, been out in the yard all day - I'll read through them later tonight and respond.
Angela MorroneAngela Morrone
Hi Tom, This was working, but students coming in for this new school year are skipping a grade - ie, it is making them a grade higher than what they are. 

For example, if the date stamp is today and the current grade is 3rd, the system is making them 4th graders. 

Doesnt this have to do with the:
IF(Today()<Date(Year(Today()),6,1),
I'm trying to figure out what the 6 and the 1 represent..
 
Steve MolisSteve Molis
Looping back in on this, the end of July got crazy busy.  The DATE funtion basically coverts 3 Numbers into YYYY MM and DD and builds a Date Value out of them. 

So  
DATE( YEAR(TODAY()), 6, 1)
So DATE Is basically saying give me 3 numbers so's I can build me a Date. 

YEAR( TODAY() ) is isaying Get the number of whatever Year today's Date is. 

And TODAY() is saying "It's September 6, 2018, here's the number 2018.  

Year says "Thanks" and hands the 2018 to DATE and DATE builds it's YYYY 

The number 6 represents June (or MM 06) 

The number 1 represents DD 01  

So DATE( YEAR(TODAY()), 6, 1)  =  DATE( 2018 , 06 , 01 )  or YYYY/MM/DD = 2018/06/01


 
Steve MolisSteve Molis
btw -  When you say you're trying to return the Year they will graduate, do you mean a YYYY value like 2018?
Tom HoffmanTom Hoffman
That formula was to calculate 'years to go,' So if you are in Grade 2 when you submit, you should have 10 more school years remaining.  School years is an important distinction, because in September 2018, 10 more years is 2028, but 10 more school years is 2029.

If you want the math to determine the year they will graduate vs the number of grades they need to complete, then we'll need to change things because:

2nd grader, Sept start has '11 more years' to finish
2nd grader, May start has '10 more years' to finish

So, I would change the <6-1-Year part of the formula to read:

'12th'=0
...
'Kindergarten'=12

 and the > 6-1-Year part of the formula to read:

'12th'=1
'Kindergarent'=13


(by the way Angela, I completely forgot I helped you with this before the most recent 40 question/10 chatter/1 screenshare marathon! Did you get that data upload squared away, everything working on that build?)
Tom HoffmanTom Hoffman
++Steve, now I have the slow fingers.
Angela MorroneAngela Morrone
Happy Friday! 

The following was missing in the original formula, so I added another IF statement..  of course, I'm now getting a syntax error
 
and the > 6-1-Year part of the formula to read:
IF(Today()<Date(Year(Today()),6,1), 
Case(Grade_When_Submitted__c, 
'12th',0, 
'11th',1, 
'10th',2, 
'9th',3, 
'8th',4, 
'7th',5, 
'6th',6, 
'5th',7, 
'4th',8, 
'3rd',9, 
'2nd',10, 
'1st',11, 
'Kindergarten',12, 
'Pre-K',13, 
Null,
IF(Today()>Date(Year(Today()),6,1), 
Case(Grade_When_Submitted__c, 
'12th',1, 
'11th',2, 
'10th',3, 
'9th',4, 
'8th',5, 
'7th',6, 
'6th',7, 
'5th',8, 
'4th',9, 
'3rd',10, 
'2nd',11, 
'1st',12, 
'Kindergarten',13, 
'Pre-K',14, 
Null))

**** So I was thinking the "6" represented the 6th month?  Ie, I was thining that June 1 was the 'transition date'   - is that incorrect?
Steve MolisSteve Molis
No, you're correct 

Breaking it down

TODAY() = 09/07/2018 
YEAR(TODAY()) = 2018 
DATE(YEAR(TODAY()), 06, 01) = 2018/06/01  ( in YYYY/MM/DD format) 
 
Steve MolisSteve Molis
PS.  Just a personal prefernce I always UPPERCASE my Formula Functions and pad the months and days 1-9 with leading 0's 
Tom HoffmanTom Hoffman
Angela - the way that IF() statement works is as follows: IF( "less than June 1, CurrentYear" , "do THIS", "otherwise do THAT") Otherwise in this case means greater than May 31, CurrentYear @Steve - I try on the Uppercase, get lazy sometimes.
Steve MolisSteve Molis
Hi Guys,
I've been chewing on this for a while and I wonder if a (slightly) more simple approach like this might work 
 
IF( TODAY() < DATE(YEAR(TODAY()),06,01), 
14 , 13 ) -
CASE(Grade_When_Submitted__c,
'12th',14,
'11th',13,
'10th',12,
'9th',11,
'8th',10,
'7th',9,
'6th',8,
'5th',7,
'4th',6,
'3rd',5,
'2nd',4,
'1st',3,
'Kindergarten',2,
'Pre-K',1,
0)

PS. 

This logic has a loophole, because 06/01/2018 is not < 06/01/2018 and it's also not > 06/01/2018 so anyone registered on 06/01 would fall through the cracks  
IF(Today()<Date(Year(Today()),6,1)... 
IF(Today()>Date(Year(Today()),6,1),

you need to say either
IF(TODAY() < =DATE(YEAR(TODAY()),06,01)... 
IF(TODAY() > DATE(YEAR(TODAY()),06,01)
or 
IF(TODAY() < DATE(YEAR(TODAY()),06,01)...  
IF(TODAY() >= DATE(YEAR(TODAY()),06,01)


 
Angela MorroneAngela Morrone
Update:
I tried Steve's formula and a 2nd grader (who is supposed to calculate to be a 1st grader) was computing to be a 10th grader so I did this:
IF( TODAY() < DATE(YEAR(TODAY()),06,01), 
14 , 13 ) - 
CASE(Grade_When_Submitted__c, 
'12th',1, 
'11th',2, 
'10th',3, 
'9th',4, 
'8th',5, 
'7th',6, 
'6th',7, 
'5th',8, 
'4th',9, 
'3rd',10, 
'2nd',11, 
'1st',12, 
'Kindergarten',13, 
'Pre-K',14, 
0)
This corrected that particular student.
But when I bounced it off one of my older ones that was 'working' (date submited would have been last year) the 5th grader became a 4th grader.

Still playing around with the info you gave me...
 
Angela MorroneAngela Morrone
I'm trying to combine two IF statements, correct?

User-added image
Angela MorroneAngela Morrone
Here's the latest attempt with Tom's formula - I'm just not succeeding in adding the additional IF statement how to add include "Otherwise in this case means greater than May 31,"
IF(TODAY()<DATE(YEAR(TODAY()),08,01), 
CASE(Grade_When_Submitted__c, 
'12th',0, 
'11th',1, 
'10th',2, 
'9th',3, 
'8th',4, 
'7th',5, 
'6th',6, 
'5th',7, 
'4th',8, 
'3rd',9, 
'2nd',10, 
'1st',11, 
'Kindergarten',12, 
'Pre-K',13, 
Null,
 AND(
IF(TODAY()<DATE(YEAR(TODAY()),7,31), 
CASE(Grade_When_Submitted__c, 
'12th',1, 
'11th',2, 
'10th',3, 
'9th',4, 
'8th',5, 
'7th',6, 
'6th',7, 
'5th',8, 
'4th',9, 
'3rd',10, 
'2nd',11, 
'1st',12, 
'Kindergarten',13, 
'Pre-K',14, 
Null)))))

 
Steve MolisSteve Molis
"greater than May 31..." would be  
TODAY() > DATE(YEAR(TODAY()),05,31)
or 
TODAY() >= DATE(YEAR(TODAY()),06,01)
What retul do you want the Formula to return in that case?



 
Steve MolisSteve Molis
Also Nested IF statements are Mutually Exclusive, if a record meets the criteria of the first IF, it is eliminated from all of the IF's after that.  If a record meets the criteria of the second IF, it is eliminated from all of the IF's after that, etc, etc...  

So your first IF
IF(TODAY()<DATE(YEAR(TODAY()),08,01)...
will eliminate all of these record too
IF(TODAY()<DATE(YEAR(TODAY()),7,31)
Because July 31st is before August 1st

Think of it like you're assigning Letter Grades based on a Test Score.  You have a stack of tests to be graded, when you assign a grade the test it doesn't go back into the same pile, you put it aside in another pile and never have to look at it and Grade it again.   
 

IF(Score >= 90, "A",
IF(Score >= 80, "B",
IF(Score >= 70, "C",
IF(Score >= 60, "D",
"F"))))

Anyone with a test score >= 90% gets an A and their tests are removed from the pile, the only ones left to be graded are the ones < 90%. 
 
Angela MorroneAngela Morrone
Thank you!  The reult is a student's "current grade" which ranges from Pre-k to 9th.

I think I'm understanding what you and Tom are saying - I just keep playing with the formula because I just can't get rid of the syntax error. I've either got too many partenthisis or not enough.
Angela MorroneAngela Morrone
I've re-read your last post 4 times now.  I can't tell if I should stay the course or if I should start over..
 
Angela MorroneAngela Morrone

 Looking like the formula has to be rewritten - can't this just be simple addition?

Am I overthinking this?

We have a student who completed the online form.  
We have captured date_submitted (2/14/17) and grade_submitted (4th) at that time

In the 'current grade' formula that I'm working in:

grade_submitted stays the same until July 1st of each year
then plus one for each year going forward

so in example above the student would be in the 6th grade
based on today's date grade_submitted plus two (because we've passed July 1st 2x since 2/14/2017) 

seems like a converstion is necessary - convert the grades from 3rd, 4th, 5th to just 3, 4 and 5

 

Tom HoffmanTom Hoffman
Angela, I think you are mixing up formulas now that I'm reading back through this whole thread. 

*Steve, not sure your idea works - I tried it out, seems like seniors would return a negative number on one side of the equation, but im probably doing something wrong. 

Record creation gives us: 
A - Submitted Date
B - Current Grade

From that we will derive:
C - Years To Go (at submission only)
D - Graduation Year
E - Current Grade

You wanted to know D & E, but we need C to figure that out. 


Starting with C, Years to Go.  This formula was supposed to be used as a field update in a Process Builder, so 'Today()' would have worked, but I think you took it to a custom field and that is where things got strange. 

I've updated it to use July 1 as its 'flip date' and to reference 'submitted date' so you can use it as a custom field or in a process builder field update (I still think it should be used in Process Builder because not sure our other formulas will compile...).

Years to Go = 
IF(Date_Submitted__c()<Date(Year(Date_Submitted__c),7,1),
Case(Grade_When_Submitted__c,
'12th',0,
'11th',1,
'10th',2,
'9th',3,
'8th',4,
'7th',5,
'6th',6,
'5th',7,
'4th',8,
'3rd',9,
'2nd',10,
'1st',11,
'Kindergarten',12,
'Pre-K',13,
0)), 
'12th',1,
'11th',2,
'10th',3,
'9th',4,
'8th',5,
'7th',6,
'6th',7,
'5th',8,
'4th',9,
'3rd',10,
'2nd',11,
'1st',12,
'Kindergarten',13,
'Pre-K',14,
0))

Here is how this formula works, based on current grade and submitted month, it returns the numbers below for 'Years to Go".  In the next formula, we are going to add YearsToGo to Date Submitted to find 'Graduation Year'
User-added image

Graduation Year (text) = 
Text(Year(Date_Submitted__c)+YearsToGo)

So now that we know years to go at the time the form was submitted AND we know the graduation year, we can figure out the current grade. We can do this by subtracting Current Year from Grad Year, adjusting the results by one depending on if its before or after July. 

IF(Today()<Date(Year(Today(),7,1),
Case((Year(Date_Submitted)+YearsToGo) -Year(Today()),
0, ‘12th’,
1, ‘11th’,
2, ‘10th’,
3, ‘9th’,
4, ‘8th’,
5, ‘7th’,
6, ‘6th’,
7, ‘5th’,
8, ‘4th’,
9, ‘3rd’,
10, ‘2nd’,
11, ‘1st’,
12, ‘Kindergarten’,
13, ‘Pre-K’,
'Graduated'),
Case((Year(DateValue(CreatedDate))+YearsToGo) -Year(Today()),
1, ‘12th’,
2, ‘11th’,
3, ‘10th’,
4, ‘9th’,
5, ‘8th’,
6, ‘7th’,
7, ‘6th’,
8, ‘5th’,
9, ‘4th’,
10, ‘3rd,’
11, ‘2nd’,
12, ‘1st’,
13, ‘Kindergarten’,
14, ‘Pre-K’,
'Graduated'))

@Steve, would love your thoughts on this one
Angela MorroneAngela Morrone
Hi there,
Playing around with the new "C" field creation.. keep getting a syntax - an extra comma
trying to get rid of it 
 
Tom HoffmanTom Hoffman
Sorry, must have deleted the second Case() function...

Years to Go = 
IF(Date_Submitted__c()<Date(Year(Date_Submitted__c),7,1),
Case(Grade_When_Submitted__c,
'12th',0,
'11th',1,
'10th',2,
'9th',3,
'8th',4,
'7th',5,
'6th',6,
'5th',7,
'4th',8,
'3rd',9,
'2nd',10,
'1st',11,
'Kindergarten',12,
'Pre-K',13,
0)),
Case(Grade_When_Submitted__c, 
'12th',1,
'11th',2,
'10th',3,
'9th',4,
'8th',5,
'7th',6,
'6th',7,
'5th',8,
'4th',9,
'3rd',10,
'2nd',11,
'1st',12,
'Kindergarten',13,
'Pre-K',14,
0))
Angela MorroneAngela Morrone
I noticed that and tried to fix myself-still being picky
User-added image
Tom HoffmanTom Hoffman
Delete the second of the )) after the last value, 0, in the first case function
Angela MorroneAngela Morrone
Spelling is verbatim, I pasted it
User-added image
Tom HoffmanTom Hoffman
That was a leftover from the today() function, didnt catch that in my editing.  Just delete the ()
Angela MorroneAngela Morrone
sooo  close!  (this is "current grade")

User-added image
Angela MorroneAngela Morrone
User-added image
Angela MorroneAngela Morrone
I just knew you were doing that!
 I'll keep at it 
Tom HoffmanTom Hoffman
Sorry, just typing this free hand...dont have a check syntax button to double check. 

In any case - this is a great exercise in troublehshooting formulas, there are three obvious misses here on my part:

1) there is a missing ) very early in the formula
2) there is an extra ) in the same place as before
3) a field reference needs to be updated

Give it a shot on your own first, if you can't find them, then I'll give the answer ;)
Angela MorroneAngela Morrone
Error: Invalid Data. 
Review all error messages below to correct your data.
Compiled formula is too big to execute (5,082 characters). Maximum size is 5,000 characters (Related field: Formula)
Angela MorroneAngela Morrone
User-added image
Angela MorroneAngela Morrone
FYI - it saved.  About to test it
IF(Today()<Date(Year(Today()),7,1), 
Case((Year(Pledge_Submitted_Date__c)+Graduation__c) -Year(Today()), 
0, '12', 
1, '11', 
2, '10', 
3, '9', 
4, '8', 
5, '7', 
6, '6', 
7, '5', 
8, '4', 
9, '3', 
10, '2', 
11, '1', 
12, 'K', 
13, 'PK', 
'Grad'), 
Case((Year(DateValue(CreatedDate))+Graduation__c) -Year(Today()), 
1, '12', 
2, '11', 
3, '10', 
4, '9', 
5, '8', 
6, '7', 
7, '6', 
8, '5', 
9, '4', 
10, '3', 
11, '2', 
12, '1', 
13, 'K', 
14, 'PK', 
'Grad'))

 
Tom HoffmanTom Hoffman
Two things: 1.change the datevalue(createddate) to just your submitted date field 2. The compile error is why I recommended to use PB to set the value.
Angela MorroneAngela Morrone
I'm totally up for the PB.. my head just can't think about how that would work .. its been a long weekend.  The above formula would require me to rebuild all my dashboards (which is fine), but I guess I will hold off and revisit the PB in the AM

User-added image
 
Tom HoffmanTom Hoffman
I think if you look at your error and my previous note on what to replace, you'll figure it out pretty quickly.
Angela MorroneAngela Morrone
Got it.
My eyes are fried at this point!
Angela MorroneAngela Morrone
Thanks Tom!  I realize the implications of shortening the grades and how that would screw up current PBs and rollups.  I'll check it out in the AM.  Have a good night.
Angela MorroneAngela Morrone
Good Morning.
I'm a bit worried that if I step away too long from this I will have a hard time remembering where things were left so  I had a hard time sleeping.

Are you able to correct my thinking on the process builder build?

Object: Contact / start proces when record is created or edited

Criteria: Conditions are Met /Field = student record type

Action: Action Type/Update Records, Action Name "Change the characters of the Grade When Submitted, Record Type/Select Cotact record that started your process

Immediate Actions: Update records to meet all conditions
Filter the records you update based on these conditions:
Grade When Pledged - equals - picklist - 1st
Grade When Pledged - equals - picklist - 2nd
Grade When Pledged - equals - picklist - 3rd
Grade When Pledged - equals - picklist - 4th

Select new values for the records you update
Grade When Pledged - Formula:
IF([Contact].Grade_When_Submitted__c='1st','1;',null)& 
IF([Contact].Grade_When_Submitted__c='2nd','2;',null)& 
IF([Contact].Grade_When_Submitted__c='3rd','3;',null)& 
IF([Contact].Grade_When_Submitted__c='4th','4;',null)& 
IF([Contact].Grade_When_Submitted__c='5th','5;',null)& 
IF([Contact].Grade_When_Submitted__c='6th','6;',null)& 
IF([Contact].Grade_When_Submitted__c='7th','7;',null)& 
IF([Contact].Grade_When_Submitted__c='8th','8;',null)& 
IF([Contact].Grade_When_Submitted__c='1st','1;',null)
 
Tom HoffmanTom Hoffman
I'm not following. 

Your PB should be populating the Years to Go field. 

Criteria would be:

RecordType.Name = Student
Submitted Date is null boolean false

Check the box under advanced so it only fires the first time. 

Field Update:
Years to Go = 
IF([Contact].Date_Submitted__c<Date(Year([Contact].Date_Submitted__c),7,1),
Case([Contact].Grade_When_Submitted__c,
'12th',0,
'11th',1,
'10th',2,
'9th',3,
'8th',4,
'7th',5,
'6th',6,
'5th',7,
'4th',8,
'3rd',9,
'2nd',10,
'1st',11,
'Kindergarten',12,
'Pre-K',13,
0),
Case([Contact].Grade_When_Submitted__c, 
'12th',1,
'11th',2,
'10th',3,
'9th',4,
'8th',5,
'7th',6,
'6th',7,
'5th',8,
'4th',9,
'3rd',10,
'2nd',11,
'1st',12,
'Kindergarten',13,
'Pre-K',14,
0))

If years to go is a stat field, the other fields should be able to be a formula without any compile issues. 
Angela MorroneAngela Morrone
Seriously been trying to do this without bothering you.
Years To Go is not showing up as an option in the field dropdown list

User-added image

User-added imageUser-added image

 
Tom HoffmanTom Hoffman
You have to create it as a Number field (18, 0 so no decimals) to be able to update it from PB.
Angela MorroneAngela Morrone
Sorry I don't understand
Years To Go is a custom formula field its output is a number field with no decimals..
Tom HoffmanTom Hoffman
Years to Go has to be a free entry number field. You can't edit/update a formula field. Years to Go has be a regular number field so you can avoid compile limits in formulas that reference Years to Go. You can set it to read only on the page layout and/or remove edit permissions to that field on profiles so people do not edit it.
Angela MorroneAngela Morrone
I found where I messed up and thought I got it all fixed..

User-added image
Tom HoffmanTom Hoffman
Your field update is supposed to be updating a number field called Years to Go.  Years to Go cannot be a formula field for all of this to work.  

Once you have Years to Go as a number field, graduation year is a formula field with a text output using this formula: Text(Year(Pledge_Submitted_Date__c)+YearsToGo)

Then Current Year is a formula field like this:

IF(Today()<Date(Year(Today(),7,1),
Case((Year(Pledge_Submitted_Date__c)+YearsToGo) -Year(Today()),
0, ‘12th’,
1, ‘11th’,
2, ‘10th’,
3, ‘9th’,
4, ‘8th’,
5, ‘7th’,
6, ‘6th’,
7, ‘5th’,
8, ‘4th’,
9, ‘3rd’,
10, ‘2nd’,
11, ‘1st’,
12, ‘Kindergarten’,
13, ‘Pre-K’,
'Graduated'),
Case((Year(Pledge_Submitted_Date__c)+YearsToGo) -Year(Today()),
1, ‘12th’,
2, ‘11th’,
3, ‘10th’,
4, ‘9th’,
5, ‘8th’,
6, ‘7th’,
7, ‘6th’,
8, ‘5th’,
9, ‘4th’,
10, ‘3rd,’
11, ‘2nd’,
12, ‘1st’,
13, ‘Kindergarten’,
14, ‘Pre-K’,
'Graduated'))
Angela MorroneAngela Morrone

I did not have a "Current Year" field created or on the radar - I am now creatng a new field called "Current Year" and I'm attaching the formula mentioned above.

I had previously deleted but now have recreated:
Years to go- a number field
Graduation year -a formula field: 

formula: Text(Year(Pledge_Submitted_Date__c)+YearsToGo__c)

 

Angela MorroneAngela Morrone
"Submitted Date is null boolean false"

I don't get it, and I probably won't get it - but in the off chance this was an errror
- I have zero records where the submitted date would be null...
 
Tom HoffmanTom Hoffman
Basically, when the pledge submitted date is populated, we want this PB to fire and figure out years to go based on that submitted date. So...we check to make sure its not null, if its not, then the PB criteria evaluates to true, and works through the actions.
Angela MorroneAngela Morrone
Its absolutely hosed.  It was somewhat working.. now its not.
​Things have been changed so much I can't make sense of anything any more.

Here is the code for "Current Grade" 
IF(Today()<Date(Year(Today()),7,1),
Case((Year(Pledge_Submitted_Date__c)+YearsToGo__c) -Year(Today()),
0, '12th',
1, '11th',
2, '10th',
3, '9th',
4, '8th',
5, '7th',
6, '6th',
7, '5th',
8, '4th',
9, '3rd',
10, '2nd',
11, '1st',
12, 'Kindergarten',
13, 'Pre-K',
'Graduated'),
Case((Year(Pledge_Submitted_Date__c)+YearsToGo__c) -Year(Today()),
1, '12th',
2, '11th',
3, '10th',
4, '9th',
5, '8th',
6, '7th',
7, '6th',
8, '5th',
9, '4th',
10, '3rd',
11, '2nd',
12, '1st',
13, 'Kindergarten',
14, 'Pre-K',
'Graduated'))


Here is a pic of this area on a student profile - all students are "Graduates"..

I've hosed the Student Pledge Status (which is connected to reports showing if schools and students are active or almost active). I simply can not save this formula which is verbatium for what was previously working (I had pasted original formula in the descirption):

User-added image

Here is Graduation Year
User-added image

Here is Years To Go
User-added image

 
Angela MorroneAngela Morrone
Here is the Update Current Year process builder

User-added image
User-added image
User-added image
User-added image
 
Tom HoffmanTom Hoffman
It's not hosed - just need a field update & things will fall into place. 

Years to Go - right now this field is blank for all existing records (remember, its a free entry number field), this is why everyone is 'graduated,' you'll need to export all ids and send them back via data loader (how I showed you on the call) to kick off that process builder.  Make sure you uncheck the box under advanced on the criteria side before doing this update, so that way it evaluates for all students.  I would also be sure to set your chunk settings to 50 or so to avoid any of your other automations causing a SOQL query limit. 

Graduation Year - this looks good to go. 

Current Grade - this looks good to go as well. 

Student Pledge Status - this is a new one you are throwing at us, but the fix is pretty easy.  First, your error is because you are referencing a text field with a number.  Instead of 1, you would put '1st' and so on.   

However, rather than reference the Current Grade text formula field (and risking running into compile limits) we can use the same formula for current grade and just change the output to be the status field you want (note, not sure you have all these status fields, just populated as an example)

IF(Today()<Date(Year(Today(),7,1),
Case((Year(Pledge_Submitted_Date__c)+YearsToGo) -Year(Today()),
0, US_School__r.x12th_Grade_Status__c,
1, US_School__r.x11th_Grade_Status__c,
2, US_School__r.x10th_Grade_Status__c,
3, US_School__r.x9th_Grade_Status__c,
4, US_School__r.x8th_Grade_Status__c,
5, US_School__r.x7th_Grade_Status__c,
6, US_School__r.x6th_Grade_Status__c,
7, US_School__r.x5th_Grade_Status__c,
8, US_School__r.x4th_Grade_Status__c,
9, US_School__r.x3rd_Grade_Status__c,
10, US_School__r.X2nd_Grade_Status__c,
11, US_School__r.X1st_Grade_Status__c,
12, US_School__r.Kindergarten_Status__c,
13, ‘Pre-K’, US_School__r.Pre-Kindergarten_Status__c
'Graduated'),
Case((Year(Pledge_Submitted_Date__c)+YearsToGo) -Year(Today()),
1, US_School__r.x12th_Grade_Status__c,,
2, US_School__r.x11th_Grade_Status__c,
3, US_School__r.x10th_Grade_Status__c,
4, US_School__r.x9th_Grade_Status__c,
5, US_School__r.x8th_Grade_Status__c,
6, US_School__r.x7th_Grade_Status__c,
7, US_School__r.x6th_Grade_Status__c,
8, US_School__r.x5th_Grade_Status__c,
9, US_School__r.x4th_Grade_Status__c,
10, US_School__r.x3rd_Grade_Status__c,
11, US_School__r.X2nd_Grade_Status__c,
12, US_School__r.X1st_Grade_Status__c,
13, US_School__r.Kindergarten_Status__c,
14, US_School__r.Pre-Kindergarten_Status__c,
'Graduated'))
Angela MorroneAngela Morrone
It doesn't seem like I should use data loader until the Student Pledge Status is fixed?

I can't understand the last section.
This looks inconsistent with the rest of the formula:
13, ‘Pre-K’, US_School__r.Pre-Kindergarten_Status__c
But is adding Pre-K necessary?  I know its showing up but its not an option on the online picklist.

I don't know if I'm following.. but I tried this and got an errorUser-added image

 
IF(Today()<Date(Year(Today(),7,1),
Case((Year(Pledge_Submitted_Date__c)+  YearsToGo__c) -Year(Today()),
0, US_School__r.x12th_Grade_Status__c,
1, US_School__r.x11th_Grade_Status__c,
2, US_School__r.x10th_Grade_Status__c,
3, US_School__r.x9th_Grade_Status__c,
4, US_School__r.x8th_Grade_Status__c,
5, US_School__r.x7th_Grade_Status__c,
6, US_School__r.x6th_Grade_Status__c,
7, US_School__r.x5th_Grade_Status__c,
8, US_School__r.x4th_Grade_Status__c,
9, US_School__r.x3rd_Grade_Status__c,
10, US_School__r.X2nd_Grade_Status__c,
11, US_School__r.X1st_Grade_Status__c,
12, US_School__r.Kindergarten_Status__c,
'Graduated'),
Case((Year(Pledge_Submitted_Date__c)+  YearsToGo__c) -Year(Today()),
1, US_School__r.x12th_Grade_Status__c,,
2, US_School__r.x11th_Grade_Status__c,
3, US_School__r.x10th_Grade_Status__c,
4, US_School__r.x9th_Grade_Status__c,
5, US_School__r.x8th_Grade_Status__c,
6, US_School__r.x7th_Grade_Status__c,
7, US_School__r.x6th_Grade_Status__c,
8, US_School__r.x5th_Grade_Status__c,
9, US_School__r.x4th_Grade_Status__c,
10, US_School__r.x3rd_Grade_Status__c,
11, US_School__r.X2nd_Grade_Status__c,
12, US_School__r.X1st_Grade_Status__c,
13, US_School__r.Kindergarten_Status__c,
'Graduated'))



 
Tom HoffmanTom Hoffman
I was just providing an example, remove/add status values as you need to. 

A few things, your return type on this formula is set to Number, I believe it should be a text field since your status fields are text. 

It also looks like its saying there is an error with a formula in your field, is YearsToGo__c the old formula field or your new, free entry number field?  
Angela MorroneAngela Morrone
I had tried both.
So tempted to delete the field and recreate it.
But I know its tied to other areas (just can't remember)
User-added image
User-added image
Angela MorroneAngela Morrone
Latest Update:
I deleted the Student Status field and recreated it with the new formula.
It is calcuating formula fields.
Error: Invalid Data. 
Review all error messages below to correct your data.
Compiled formula is too big to execute (7,203 characters). Maximum size is 5,000 characters (Related field: Formula)

User-added image
Angela MorroneAngela Morrone
User-added image
Tom HoffmanTom Hoffman
Ok...we are mixing up too many things here.  

First, your error message on Student Pledge Status suggests Student Pledge Status is being used somewhere else in the SF org and that field is not able to compile any more. 

What is the field referencing that field?  Is knowing the status of that students grade at their school important at the student level for the business requirements? If not, I would move on from it - its easily available by looking at the school record, which means its easily available in reporting. 

Your DLRS error looks like its saying, you changed Current Grade to output in some non-string format (like a number), is it still a text field? 



 
Angela MorroneAngela Morrone
Hi, The answer is yes to both questions.
Huge business requirement and yes Current grade is text field.
Angela MorroneAngela Morrone
On Student Status, I removed K-5th from the formula (below) and was able to avoid the compile error and all my dashboards came back to life. 

I started to wonder if 'Graduated' was removed (since I don't need that report) - maybe I can add a few more grades back into the formula.

IF(Today()<Date(Year(Today()),7,1), 
Case((Year(Pledge_Submitted_Date__c)+YearsToGo__c) -Year(Today()), 
4, US_School__r.X8th_Grade_Status__c, 
5, US_School__r.X7th_Grade_Status__c, 
6, US_School__r.X6th_Grade_Status__c, 
'Graduated'), 
Case((Year(Pledge_Submitted_Date__c)+YearsToGo__c) -Year(Today()), 
5, US_School__r.X8th_Grade_Status__c, 
6, US_School__r.X7th_Grade_Status__c, 
7, US_School__r.X6th_Grade_Status__c, 
'Graduated'))
Angela MorroneAngela Morrone
Regarding the DLRS error - I received it when I tried to fix a School Name on a Contact Record.

I was emailed the following APEX email:
Apex script unhandled trigger exception by user/organization: 005f2000008y2gu/00Df2000001Kv2H

dlrs_ContactTrigger: execution of AfterUpdate

caused by: System.QueryException: value of filter criterion for field 'Current_Grade__c' must be of type string and should be enclosed in quotes

Class.dlrs.LREngine.QueryExecutor.query: line 662, column 1
Class.dlrs.LREngine.WithoutSharingQueryExecutor.query: line 680, column 1
Class.dlrs.LREngine.rollUp: line 238, column 1
Class.dlrs.LREngine.rollUp: line 117, column 1
Class.dlrs.RollupService.updateMasterRollupsTrigger: line 1035, column 1
Class.dlrs.RollupService.handleRollups: line 892, column 1
Class.dlrs.RollupService.triggerHandler: line 311, column 1
Trigger.dlrs_ContactTrigger: line 7, column 1

User-added image
Angela MorroneAngela Morrone
Also - Reporting on the status of grades 9-12 are not part of the business requirements.  
Thanks
Angela MorroneAngela Morrone
I am scheduled to do a demo of the org today.. I'm not sure what to do.. I can't tell if this is fixable...?
 
Tom HoffmanTom Hoffman
Did you check the filter criteria on your DLRS rollup? It is saying you have to wrap the filter criteria with ' ' if nots already.
Angela MorroneAngela Morrone
Yes, I tried that 
User-added image
Angela MorroneAngela Morrone
who, hang on..  I put the ' ' in just the 6
Angela MorroneAngela Morrone

That edit allowed me to save the DLRS and presented a great deal  of hope.
I was previously unable to do anything at all.

I surrounded the single quote to all the grades and ran 'calculate' on all of them.

The inital error is still happening - it happens when I go into a student contact record and I change the school name (which is a lookup field).

User-added image

Tom HoffmanTom Hoffman
You have to update all of your DLRS records. Your current grade formula outputs like this '6th' '7th', etc not 6 or 7, so be sure to update them all appropriately.
Angela MorroneAngela Morrone
I ran 'calculate' on all of them - not sure how long that takes.
But need to make sure you aren't saying I need to update all contacts via data loader?
Tom HoffmanTom Hoffman
I was saying you need to update your DLRS records to use the correct syntax of "6th", "7th", "8th" etc; they can't be just numbers with " " and they can be just numbers with " " like "6", "7", because Current Grade outputs with "1st", "2nd", "3rd", etc.
Angela MorroneAngela Morrone

k, I think you meant to say "and they can't be just number with... "
Does this look correct?
User-added image

 

Tom HoffmanTom Hoffman
Yup, looks good, if you do that for all of them, should work and not be any errors.
Angela MorroneAngela Morrone
k.. thx!
it looks like it reverts back even if I thought I saved.. seems I have to deactivate first
Angela MorroneAngela Morrone
User-added image
Thought you could use this in your library.
Angela MorroneAngela Morrone
It looks beautiful, Tom.  Thank you for sticking it out with me.
My grade counts are off, I still have a complile issue - so many students are showing up as 'graduated'.

Below is Student Pledge Status.  It feeds into active vs. almost active.  I had to take out K-5th grade to get it working.  If I add any more lines, I get a too many characters error message.
  Can you remind me what your suggestion was to fix this?
IF(Today()<Date(Year(Today()),7,1), 
Case((Year(Pledge_Submitted_Date__c)+YearsToGo__c) -Year(Today()), 
4, US_School__r.X8th_Grade_Status__c, 
5, US_School__r.X7th_Grade_Status__c, 
6, US_School__r.X6th_Grade_Status__c, 
'Graduated'), 
Case((Year(Pledge_Submitted_Date__c)+YearsToGo__c) -Year(Today()), 
5, US_School__r.X8th_Grade_Status__c, 
6, US_School__r.X7th_Grade_Status__c, 
7, US_School__r.X6th_Grade_Status__c, 
'Graduated'))



 
Tom HoffmanTom Hoffman
Last two pointers - #1 All of those students appear as graduated because they need to be run through that process builder first (you need export all students where Current Grade = graduated, just their contact IDs, then update those records, only map the contact Id and it will trigger the Process Builder. Again, I would set your batch size down to like 25 or 50 for this update, there are a lot of related things happening and that will help you avoid collision detection & soql query limits. #2 Grade status -If you were using lightning it would be simple, we would use a quick action & related record component to display those values, but from what I saw, they were still on classic. If it really has to be there, you'll have to push the status from a PB on the school object down to free text entry fields on the related student records instead of using a formula. Your scheduled processes that kick off each night should update all the school records, which in turn should cause your PB to update the students.
Angela MorroneAngela Morrone
1.) Yes, I did this yesterday.. I'll rerun it again now.
2.) I'm spending about 90% of my time in Lightning... 
 
Tom HoffmanTom Hoffman
Make sure #1 that your criteria is set to appropriately evaluate - you have want to clone a new version temporarily that has no criteria for firing, run your update, then deactivate that vers, reverting to the prior. 

For #2, just follow the directons here.  You can create a quick action to display all of the school grade statuses or make a conditional component for each grade so the right one appears for each student.

https://theaccidentaladmins.com/conditional-record-detail-components
 https://theaccidentaladmins.com/record-detail-tabs
Angela MorroneAngela Morrone
Is there a knowledge base article on number #1 that I can read?  I'm not understanding...
Angela MorroneAngela Morrone
Tom, do you see any issues in using this code as a workaround to item #2?  It compiles.
 
Case(((Year(Pledge_Submitted_Date__c)+YearsToGo__c) - Year(Today())) + 
IF(Today()<Date(Year(Today()),7,1), 1, 0), 
1, US_School__r.X12th_Grade_Status__c, 
2, US_School__r.X11th_Grade_Status__c, 
3, US_School__r.X10th_Grade_Status__c, 
4, US_School__r.X9th_Grade_Status__c, 
5, US_School__r.X8th_Grade_Status__c, 
6, US_School__r.X7th_Grade_Status__c, 
7, US_School__r.X6th_Grade_Status__c, 
8, US_School__r.X5th_Grade_Status__c, 
9, US_School__r.X4th_Grade_Status__c, 
10, US_School__r.X3rd_Grade_Status__c, 
11, US_School__r.X2nd_Grade_Status__c, 
12, US_School__r.X1st_Grade_Status__c, 
13, US_School__r.Kindergarten_Status__c, 
'Graduated')

 
Angela MorroneAngela Morrone
ugg.
I had done the  opposite prior to your warning:
"you need export all students where Current Grade = graduated, just their contact IDs, then update those records, only map the contact Id and it will trigger the Process Builder. Again, I would set your batch size down to like 25 or 50 for this update, there are a lot of related things happening and that will help you avoid collision detection & soql query limits."

I can't fix. 

I have students who had an original grade of 8th last year showing up as 10th graders.  I exported all contacts; deactivated all PBs, uploaded just the ID match via data loader.  

I don't know what you are asking me to do.

"Make sure #1 that your criteria is set to appropriately evaluate - you have want to clone a new version temporarily that has no criteria for firing, run your update, then deactivate that vers, reverting to the prior."

Which PB are  you talking about?  All of theme? 

Please tell me if you want me to post this to another message board.
Thanks
Tom HoffmanTom Hoffman
Deactive the current PB that sets years to go, clone it. Change the criteria for the Years to Go formula to 'No Criteria' for now & activate it. Run your update so all records will populate the years to go based on the form submitted date. This should correct the records since Submitted Date is static and Grade when Submitted is static as well. Once you've done that, deactivate the new vers of the PB with 'No Criteria' and activate the previous version that has criteria.
Angela MorroneAngela Morrone
  1. DONE Deactive the current PB that sets years to go, clone it.
  2. DONE (Assuming the 'No Criteria' option is in the second step of the PB where one would "Define Criteria for this Action Group") Change the criteria for the Years to Go formula to 'No Criteria' for now & activate it.
  3. DONE (Assuming "& activate it" is the same as "run your update".  If "Run your update" means upload all contacts via Data Loader, matching only the ID field, I've done that 2x in the past 48 hours) Run your update so all records will populate the years to go based on the form submitted date.
Not sure how long PB takes to fully process

User-added image
Tom HoffmanTom Hoffman
A pb only fires when records are created or updated. Need to redo your update
Angela MorroneAngela Morrone
Will do that now.
I did test update a record and it still showed 2 grade years ahead instead of one.
Angela MorroneAngela Morrone
Updated all records; no change.
Angela MorroneAngela Morrone
Trying to figure out the prob.
I had to remove 12th because of too many characters (and its not part of the business requiements) 
Do you think that would cause the glitch?
 
IF(Pledge_Submitted_Date__c<Date(Year(Pledge_Submitted_Date__c),7,1), 
Case(Grade_When_Submitted__c, 
'11th',1, 
'10th',2, 
'9th',3, 
'8th',4, 
'7th',5, 
'6th',6, 
'5th',7, 
'4th',8, 
'3rd',9, 
'2nd',10, 
'1st',11, 
'Kindergarten',12, 
'Pre-K',13, 
0), 
Case(Grade_When_Submitted__c, 
'11th',2, 
'10th',3, 
'9th',4, 
'8th',5, 
'7th',6, 
'6th',7, 
'5th',8, 
'4th',9, 
'3rd',10, 
'2nd',11, 
'1st',12, 
'Kindergarten',13, 
'Pre-K',14, 
0))

 
Tom HoffmanTom Hoffman
Attached the wrong video in my previous post. 

Ang - really reached the limits of what I can do to help with advice and troubleshooting; you'll need to use all the messages and notes to put this together, I assure you its all here.

If you find you can't get it working, I think hiring someone to assist would be a good idea. 

I have attached a video with everything working as designed; you can see that as long as my submitted date & grade when submitted are entered correctly, all my other fields (years to go, current grade, graduation year) all update without issue.  If you find your numbers are off, it has to do with the data being input in those two fields. 

Best of luck!

https://failblazer-dev-ed.my.salesforce.com/sfc/p/61000000XlPw/a/61000000g1Pf/lwMQSdNAmQ_9EyVPwHaxe03UROwyXIo9ghOcEjKQezs
Angela MorroneAngela Morrone
I understand.
Thank you for telling me.
Angela MorroneAngela Morrone
In the off chance that someone else is following along - I'm trying to salvage a huge time investment.

The formula below references YEAR. 
A custom field in the above references something called "Graduation Year".   
 
Here is what Tom said: "So now that we know years to go at the time the form was submitted AND we know the graduation year, we can figure out the current grade. We can do this by subtracting Current Year from Grad Year, adjusting the results by one depending on if its before or after July. 


I'm obviously not an expert on formulas - but below is the Current Grade formula - Is there zero possibility that the use of YEAR (as system formula) is being used interchangablly with the custom field above called "Grad Year"?

 
IF(Today()<Date(Year(Today()),7,1), 
Case((Year(Pledge_Submitted_Date__c)+YearsToGo__c) -Year(Today()), 
0, '12th', 
1, '11th', 
2, '10th', 
3, '9th', 
4, '8th', 
5, '7th', 
6, '6th', 
7, '5th', 
8, '4th', 
9, '3rd', 
10, '2nd', 
11, '1st', 
12, 'Kindergarten', 
13, 'Pre-K', 
'Graduated'), 
Case((Year(Pledge_Submitted_Date__c)+YearsToGo__c) -Year(Today()), 
1, '12th', 
2, '11th', 
3, '10th', 
4, '9th', 
5, '8th', 
6, '7th', 
7, '6th', 
8, '5th', 
9, '4th', 
10, '3rd', 
11, '2nd', 
12, '1st', 
13, 'Kindergarten', 
14, 'Pre-K', 
'Graduated'))



 
Angela MorroneAngela Morrone
After very thourough investigation, I have concluded that all students who have a submitted date prior to July 1, 2017 skip a grade so their current grade is showing one year more than it should be.
Angela MorroneAngela Morrone
Stepped back. Saw the error.  Stupidity awareness ensued.  Crawling into a cave now. Case closed.  
Tom HoffmanTom Hoffman
Great, sounds you like you figured it out. hope its all working well. I am 99% sure with all the information provided, you have what you need, just have to work through it. good luck!