Help

Comparing date field with date from lookup fails

Solved
Jump to Solution
1479 2
cancel
Showing results for 
Search instead for 
Did you mean: 
ajp-jon
4 - Data Explorer
4 - Data Explorer

In my CRM, I am trying to compare two date values in a formula, in order to figure out which "Next step" value is the most recent. In my Interactions table, {Last contact lookup} looks up a value in my Clients table which is the output of a rollup that outputs the MAX date value from interactions for a given Client record. {Actual interaction date} is the result of a formula that compares the Interaction record's creation timestamp to an optional user-entered value, and outputs the user-entered value if it's non-zero.

Screenshot from 2023-01-08 16-09-19.png

My formula is simple: IF({Last contact lookup}={Actual interaction date},{Next step}). Unfortunately, that formula fails to produce results. The fourth row should have "Find information" in the Last next step column. Any ideas why this doesn't work?

Just looking at the screenshot above, it looks like a problem with formatting, given how the timezone appears. But Airtable understands the lookup value as a date:

Screenshot from 2023-01-08 16-17-26.png

My only other thought is that the problem is that Next step is a Single select format. Any help resolving this puzzle is much appreciated!

1 Solution

Accepted Solutions
TheTimeSavingCo
18 - Pluto
18 - Pluto

Sigh, please don't ask me why this works because I have absolutely no idea, but could you try:
1. Creating a new formula field called "Formula" or whatever, and give it the formula "{Last contact lookup}"
  - This should give you a formula field that has the exact same output of `Last contact lookup`
2. Modify the formula in `Last next step` to be `IF({Formula}={Actual interaction date},{Next step})`

And here's an example of it working:

Adam_TheTimeSav_0-1673243942507.png

 


Hopefully someone will be able to explain why this works to us hah

See Solution in Thread

2 Replies 2
TheTimeSavingCo
18 - Pluto
18 - Pluto

Sigh, please don't ask me why this works because I have absolutely no idea, but could you try:
1. Creating a new formula field called "Formula" or whatever, and give it the formula "{Last contact lookup}"
  - This should give you a formula field that has the exact same output of `Last contact lookup`
2. Modify the formula in `Last next step` to be `IF({Formula}={Actual interaction date},{Next step})`

And here's an example of it working:

Adam_TheTimeSav_0-1673243942507.png

 


Hopefully someone will be able to explain why this works to us hah

Thanks! That does solve the immediate problem, but like you say, it'd be great to know why.