Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

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

Topic Labels: Formulas
Solved
Jump to Solution
1592 4
cancel
Showing results for 
Search instead for 
Did you mean: 
GabrielViger
5 - Automation Enthusiast
5 - Automation Enthusiast

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:

formula-result.png

Can someone please help me figure it out?

Thanks 🙂

 

1 Solution

Accepted Solutions
GabrielViger
5 - Automation Enthusiast
5 - Automation Enthusiast

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

See Solution in Thread

4 Replies 4
jwag
6 - Interface Innovator
6 - Interface Innovator

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
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi @jwag,

Adding the date format didn't change anything.

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

Screenshot 2023-10-12 at 11.37.48 AM.png

Should I raise a support ticket?

Thanks for your help on this? 🙂

Josh_Colina
6 - Interface Innovator
6 - Interface Innovator

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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