IF formula failing on Lookup Value

I am using a simple formula:

IF(TDay > TODAY(), 1, 0) 

It works fine in a table where TDay is a field containing Date values.

However when I use the formula in another table where TDay is a field containing Date values that are pulled from another table via a lookup then the formula does not work (it either provides all 0s or all 1s and does not perform the evaluation function).

Any thoughts on what I’m doing wrong? Thanks in advance.

Welcome to the community, @Toby_Murdock! :smiley:

The lookup field is the source of the problem. Long story short, lookup fields will often (more often than not) return an array when queried by a formula, even when that array only contains a single value. That means that you end up comparing an array to a date, which doesn’t work.

To make this work, change your lookup field to a rollup field. Rollup fields are very similar to lookup fields, but they add an aggregation formula that lets you process the array into something more useful. In this case, you could use an aggregation formula of MAX(values). This is often good for finding the largest date in a collection of rolled-up dates, but it will also work with a single date to return just the date itself, which can then be used to compare against other dates in other fields.

3 Likes

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.