Skip to main content

Hello everyone,

Trying to come up with a formula using Chat GPT (and some tweaking) to create a formula field type that displays a subscription's (record) amount in a cell based on budget status and the payment schedule.

I want to create a formula that checks the following conditions:

  1. The Payment Schedule is "Annually."
  2. And one of the following is met:
    • a. Budget Status is "Projected" and Renewal Date is within the specified range.
    • b. Budget Status is "Actual" and Purchase Date is within the specified range.

If these 2 conditions are met, it displays the {Subscription Amount}. If none of the conditions are met, it will display "Conditions not met".

Here's the code:

 

 

IF(
AND(
{Payment Schedule} = "Annually",
OR(
AND(
{Budget Status} = "Projected",
{Renewal Date} > DATETIME_PARSE("2023-01-01"),
{Renewal Date} < DATETIME_PARSE("2023-02-01")
),
AND(
{Budget Status} = "Actual",
{Purchase Date} > DATETIME_PARSE("2023-01-01"),
{Purchase Date} < DATETIME_PARSE("2023-02-01")
)
)
),
{Subscription Amount},
"Conditions not met"
)

 

 

I keep getting the Conditions not met message even tho one of my records matches the conditions:

Can someone please help me figure it out?

Thanks 🙂

 

Hi Gabriel,

Have you tried adding a date format to the datetime_parse formulas? EG:

DATETIME_PARSE("2023-01-01", "YYYY-MM-DD")

Failing that, check that your date lookup fields are formatted as dates.

Edit field -> Formatting -> Date format -> ISO

Thanks,

Josh

Hi Gabriel,

Have you tried adding a date format to the datetime_parse formulas? EG:

DATETIME_PARSE("2023-01-01", "YYYY-MM-DD")

Failing that, check that your date lookup fields are formatted as dates.

Edit field -> Formatting -> Date format -> ISO

Thanks,

Josh


Hi @jwag,

Adding the date format didn't change anything.

My lookup fields are formatted as dates using the ISO option:

Should I raise a support ticket?

Thanks for your help on this? 🙂

Hi Gabriel! You might want to use IS_BEFORE() and IS_AFTER() to describe the relationship between the {Renewal Date} and your DATETIME_PARSE(). ">" and "<" are numerical operators, so they won't work correctly on a datetime output. You could try something like the below:

 

IF(
AND(
{Payment Schedule} = "Annually",
OR(
AND(
{Budget Status} = "Projected",
IS_AFTER(
{Renewal Date},
DATETIME_PARSE("2023-01-01")
),
IS_BEFORE(
{Renewal Date},
DATETIME_PARSE("2023-02-01")
)
),
AND(
{Budget Status} = "Actual",
IS_AFTER(
{Purchase Date},
DATETIME_PARSE("2023-01-01")
),
IS_BEFORE(
{Purchase Date},
DATETIME_PARSE("2023-02-01")
)
)
)
),
"Subscription Amount",
"Conditions not met"
)

 

Hi Everyone, 

I made some trials and errors and made the formula work by writing it like this:

IF(
AND(
{Payment Schedule} = "Annually",
OR(
AND(
{Budget Status} = "Projected",
{Renewal Date (from Subscriptions)} > ("2023-01-01"),
{Renewal Date (from Subscriptions)} < ("2023-02-01")
),
AND(
{Budget Status} = "Actual",
{Purchase Date (from Subscriptions)} > ("2023-01-01"),
{Purchase Date (from Subscriptions)} < ("2023-02-01")
)
)
),
{Subscription Amount},0
)

Thank you everyone for having a look 🙂

Gabriel

Reply


Terms & Conditions