Conditions to display amount - What's wrong with my formula?

[GabrielViger]

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 🙂

[jwag]

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

[GabrielViger]

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? 🙂

[Josh_Colina]

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

[GabrielViger · Accepted solution]

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