IF AND Formula?

[Doug_Ardy]

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?

[JonathanBowen · Accepted solution]

Hi @Doug_Ardy - this should do the trick:

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

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

JB