Help

Discover what data silos are costing your org in our commissioned Forrester study. Learn more

Formula that will indicate 2 stages

Topic Labels: Formulas
Solved
Jump to Solution
127 4
cancel
Showing results for 
Search instead for 
Did you mean: 

Hi

We would really appreciate some assistance with one of our formula fields.

Background - also see screenshot:

In our first column, we have a date field called “Lifetime Last Payment”, which we manually complete.
In our second column, we have a formula field called “Lifetime Next Payment” with the following formula:
IF({Lifetime Last Payment}=BLANK(),BLANK(), DATEADD({Lifetime Last Payment},1,“months”))
Which automatically calculates by taking the date from “Lifetime Last Payment” and adding one month.
Our third column has a formula field called ” Lifetime Payment Status” with the following formula:
IF({Lifetime Next Payment}<=TODAY(),“ :triangular_flag_on_post: Change Last Payment date”, BLANK())
In our fourth column, we have another formula field called “Final Lifetime Instalment Date”, which calculates the final date of payment for each individual line based on the instalment criteria.
Requirement:
We need help adding/updating our third column formula: IF({Lifetime Next Payment}<=TODAY(),“ :triangular_flag_on_post: Change Last Payment date”, BLANK())
To include a second status if the date in “Lifetime Next Payment” matches the date in “Final Lifetime Instalment Date”.
We need the second status to be “:green_check_mark: Paid in full”.

Thank you
Snip20221014_1

1 Solution

Accepted Solutions

Thanks Adam, it is working now.
I just had to change one last thing; so the formula now reads as:

IF(
{Lifetime Last Payment} = {Final Lifetime Instalment Date},
“ :white_check_mark: Paid in full”,
IF(
{Lifetime Next Payment} <=TODAY(),
“ :triangular_flag_on_post: Change Last Payment date”
)
)

Thanks for all your help, I really appreciate it.

See Solution in Thread

4 Replies 4

Hi Tiaan, give this a try:

 IF(
  {Lifetime Next Payment} = {Final Lifetime Instalment Date},
  ":green_check_mark: Paid in full",
  IF(
    {Lifetime Next Payment} <=TODAY(),
    ":triangular_flag_on_post: Change Last Payment date"
  )
)

I assumed that, if “Lifetime Next Payment” matches the date in “Final Lifetime Instalment Date”, you’d want it to show “:green_check_mark: Paid in full” and not show “ :triangular_flag_on_post: Change Last Payment date”

Lemme know if that’s not the case

You also don’t need to use BLANK() to indicate no value here so I removed it

Hi Adam,

Thank you for your response and help.
The formula is not working, see screenshot attached.

Snip20221018_15

Ah, it’s the curly quotes I think. I’ve edited the original code; give that a shot

Thanks Adam, it is working now.
I just had to change one last thing; so the formula now reads as:

IF(
{Lifetime Last Payment} = {Final Lifetime Instalment Date},
“ :white_check_mark: Paid in full”,
IF(
{Lifetime Next Payment} <=TODAY(),
“ :triangular_flag_on_post: Change Last Payment date”
)
)

Thanks for all your help, I really appreciate it.