Formula that will indicate 2 stages

[Tiaan_Terblanch]

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

[TheTimeSavingCo]

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

[Tiaan_Terblanch]

Adam_TheTimeSavingCo:

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”
  )
)

Hi Adam,

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

[TheTimeSavingCo]

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

[Tiaan_Terblanch · Accepted solution]

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.