How do I return the maximum date from multiple date fields but only if not greater than today?

  • 0
  • 1
  • Question
  • Updated 3 months ago
  • In Progress
Ultimately I want to send a reminder on the same date as a specific recurring event from a list of 15 multiple date fields.  So I am trying to create a formula field that returns the maximum date that is not greater than today.
I can get the max - but I haven't been able to use an IF function to exclude dates in the future.

Here is an example using just 3 of the 15 fields:
Max([MRI 3 Month], [MRI 6 Month],[MRI  9 Month])

Your help is appreciated.  I find QuickBase so useful in our healthcare environments, but unfortunately, my nursing training is not helpful when trying to write formulas!
Photo of sheree brown

sheree brown

  • 150 Points 100 badge 2x thumb

Posted 3 months ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
Try this:
Max(
  If([MRI 3 Month] <= Today(), [MRI 3 Month]),
  If([MRI 6 Month] <= Today(), [MRI 6 Month]), 
  If([MRI 9 Month] <= Today(), [MRI 9 Month])
)

(Edited)