DATETIME_DIFF not working when entered as a result to an IF statement

[Burner]

Hi everyone,

I’m unable to get a DATETIME_DIFF formula to work the way I want it to. Basically, I have a Status field as a single select as follows:

My goal is to have a column that shows me the number of Days since a record was created. When I use the DATETIME_DIFF by itself, it works just fine as below, showing 0 days

However, what I want to do is to show this only when the Status column is not equal to complete. So I’m trying to wrap my DATETIME_DIFF as the true result when the Status != “Complete”.

But this doesn’t work. When I set the status to complete, I get the false result of the IF formula, which is “Complete”, so that part works fine. But when I change the status to anything other than “Complete”, the formula shows me a blank.

I can’t figure out why this isn’t working. I tried swapping the IF formula for a SWITCH, but I have the same issue. The DATETIME_DIFF doesn’t show a result.

Edit #1: I forgot to mention that I’ve also set a (native Airtable) automation such that whenever a new record is created in this table (or entered through an Airtable form, which is another view I’ve created), the automation automatically selects the Status value as “To Do”. Could this be the reason it isn’t working?

Any help will be much appreciated.

Thank you.

[augmented]

Hi. I replicated your example and it works for me. At first I thought it might be an issue with two different “types” of return values. One a string and the other an integer. However, that doesn’t seem to be an issue.

I will also tell you that your ordering of {Date logged} and TODAY() in the DATETIME_DIFF function will produce negative values.

[Burner]

Thank you. That’s strange, it still doesn’t work for me. I realize I missed one piece of information and perhaps that could be causing the issue. I’ve set an (native Airtable) automation such that whenever a new record is created in this table (or from an Airtable form, another view I created), the automation sets the default status value as “To Do”.

Also, could you please explain what you meant by the ordering of {Date logged} and TODAY() potentially causing a negative result?

Thanks so much once again for the help.

[augmented]

Just that DATETIME_DIFF({Date logged}, TODAY(), ‘days’) will return a negative number if {Date logged} is before today (which I assume it is in general).

I wouldn’t think the automation would cause a sequence of evaluations that would allow the formula to perform badly. You could check by changing the status to something other than Complete or To Do.

[Burner]

Thank you. I did check by changing the status to:

“To Do” → Formula shows blank
“In progress” → Formula shows blank
“Laptop in for repair” → Formula shows blank
“Complete” → Formula results in “Complete”, which is the false criteria of the formula (so that part works)

And like I said, it doesn’t work even when I try a SWITCH instead of an IF. I can’t seem to figure out why this is not working. If possible, can you please share a screenshot of when you replicated this and it worked?

Thanks once again, I really appreciate you helping out with this.

[augmented · Accepted solution]

I used different single select values than you (and field names) but you get the gist…

[Burner]

Thanks a ton. This was very helpful.