Skip to main content
Solved

IF formula with a specific - not relative - date

  • September 25, 2019
  • 2 replies
  • 42 views
Melissa_Frank1
Forum|alt.badge.img+10

I have one table of people, and another with records of their visits. In the people table, I am trying to add a field that will tell me the date of the earliest visit… after 9/30/2016.

I tried adding a formula field in the visits table to return the date of the visit only if it is after 9/30/16, but I have no idea how to build a specific date into the formula. I really don’t want to add another field to the table that will force me to add “9/30/16” to every single visit record just so I can do an IS_AFTER formula with two date fields. (Which reminds me, why don’t default values you set for certain fields show up when you add new records in the mobile app? Real bummer. But I digress.)

IF(IS_AFTER(DATE,9/30/16),DATETIME_FORMAT(DATE,‘MM/DD/YYYY’),"") returns #ERROR everywhere. I am really hoping my format “9/30/16” is the problem, but I have tried other variations with no success.

Meanwhile, if it is possible to get that formula to work, will a roll-up field in the people table let me pull the MIN from that calculated field? Is there a better way to do this? I’d do it manually, but I have a couple hundred people records.

Best answer by JonathanBowen

Hi @Melissa_Frank1 - try this:

In your Visits table have a field that uses this formula:

IF(IS_AFTER(Date, '2019-06-01'), Date)

To show the visit date if it is after the specified date (modify as required) or nothing if it is before the specified date:

Back in your People table you can roll up this field (and the date field if you want) to show the earliest visit after the specified date:

The rollup field uses the MIN(values) setting:

JB

2 replies

JonathanBowen
Forum|alt.badge.img+18
  • JonathanBowen
  • Inspiring
  • Answer
  • September 26, 2019

Hi @Melissa_Frank1 - try this:

In your Visits table have a field that uses this formula:

IF(IS_AFTER(Date, '2019-06-01'), Date)

To show the visit date if it is after the specified date (modify as required) or nothing if it is before the specified date:

Back in your People table you can roll up this field (and the date field if you want) to show the earliest visit after the specified date:

The rollup field uses the MIN(values) setting:

JB

Melissa_Frank1
Melissa_Frank1
Forum|alt.badge.img+10
  • Melissa_Frank1
  • Author
  • Known Participant
  • September 26, 2019

Hi @Melissa_Frank1 - try this:

In your Visits table have a field that uses this formula:

IF(IS_AFTER(Date, '2019-06-01'), Date)

To show the visit date if it is after the specified date (modify as required) or nothing if it is before the specified date:

Back in your People table you can roll up this field (and the date field if you want) to show the earliest visit after the specified date:

The rollup field uses the MIN(values) setting:

JB


This worked! Thank you so much!

JonathanBowen
Terms & ConditionsAccessibility statement