Help

Re: Increase field number one when automated date field changes

1056 2
cancel
Showing results for 
Search instead for 
Did you mean: 
DonedaBailey
4 - Data Explorer
4 - Data Explorer

My table is for students in a tutoring program.  One field is their current grade.  Another is the calculated formula of today's date.  Every August 15th (each year) I want each student's current grade to increase by 1.  Is there a way to do this?

 

3 Replies 3
TheTimeSavingCo
18 - Pluto
18 - Pluto

Edit - Answer was wrong, deleted

With this formula, will the grade field automatically increase every August 15th?

Ah so sorry about that, the formula was flipped around I think

Try having:
1. Date of enrollment
2. Grade at enrollment date
3. Current grade

"Current grade" would be a formula field that would find the year difference between the enrollment date and Aug 15 of this year, resulting in something like this: 

Screenshot 2023-05-31 at 4.28.25 PM.png

 
Here's the formula I used:

 

{Grade at enrollment} + 
DATETIME_DIFF(
  {Current Date}, 
  "08/15/" & YEAR({Date of enrollment}),
  "years"
) + 
1 - 
IF(
  AND(
    IS_BEFORE(
      {Current Date}, 
      "08/15/" & YEAR({Current Date})
    ),
    IS_SAME({Current Date}, {Date of enrollment}, 'year')
  ),
  1
)

 

And here's a link to the base

Sorry about that.  I'm actually still not too sure whether this does what you're looking, please let me know if there's something else wrong with it and I'll do what I can to fix it!