Help

IF AND Formula?

Topic Labels: Formulas
Solved
Jump to Solution
848 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Doug_Ardy
6 - Interface Innovator
6 - Interface Innovator

I can’t seem to figure out this formula. Here is what I would like to do.

  1. Compares today’s date with a specific field date. If today’s date is after field date I want to multiple field A by 10. If today’s date is before field date, then I want to multiple field A by 50.

  2. Regardless of which calculation is done above, if the sum of the calculation is 1000 or above, then I want 1000 returned instead.

For example, let’s say field A has a value of 50. If today’s date is after the field date, then the value returned would be 500. If however, today’s date was before the field date, then the value returned would be 1000 (even though the calculation would by 50 x 50 = 2500, the 1000 limit would override it).

For the life of me, I can’t figure this out. Can anyone help?

1 Solution

Accepted Solutions
JonathanBowen
13 - Mars
13 - Mars

Hi @Doug_Ardy - this should do the trick:

MIN(
  IF(Date >= TODAY(), 
  {Field A} * 10, 
  {Field A} * 50), 
1000) 

Screenshot 2020-03-18 at 21.52.08

So, this is returning the minimum of the IF() statement and 1000.

JB

See Solution in Thread

1 Reply 1
JonathanBowen
13 - Mars
13 - Mars

Hi @Doug_Ardy - this should do the trick:

MIN(
  IF(Date >= TODAY(), 
  {Field A} * 10, 
  {Field A} * 50), 
1000) 

Screenshot 2020-03-18 at 21.52.08

So, this is returning the minimum of the IF() statement and 1000.

JB