Help

Re: IF formula with a specific - not relative - date

Solved
Jump to Solution
569 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Melissa_Frank1
6 - Interface Innovator
6 - Interface Innovator

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.

1 Solution

Accepted Solutions
JonathanBowen
13 - Mars
13 - Mars

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:

Screenshot 2019-09-26 at 11.30.17.png

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:

Screenshot 2019-09-26 at 11.31.20.png

The rollup field uses the MIN(values) setting:

Screenshot 2019-09-26 at 11.31.29.png

JB

See Solution in Thread

2 Replies 2
JonathanBowen
13 - Mars
13 - Mars

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:

Screenshot 2019-09-26 at 11.30.17.png

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:

Screenshot 2019-09-26 at 11.31.20.png

The rollup field uses the MIN(values) setting:

Screenshot 2019-09-26 at 11.31.29.png

JB

This worked! Thank you so much!