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