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

Terms & ConditionsAccessibility statement