Formula brings error due to empty date column

Platform discussions Special workflows & use cases
,
1

Hey everyone,
I have a formula which returns an error when the Date Column, I am using, is empty.

round({Total Cash Value Now}/1.05^(DAYS({Due Date},TODAY())/360)

When I try to avoid the error by applying an IF formula it won’t work either:

So if I use:
if({Due Date}=“”,0,round({Total Cash Value Now}/1.05^(DAYS({Due Date},TODAY())/360),0)) I still get an error.

The DAYS formula brings back an empty value but not nil. I tried making a simple formula like this:
DAYS({Due Date},TODAY())=“” which returns false.

However, I think the problem is that Monday even goes into that part of the logic after applying the IF function in the first place:
IF({Due Date}=“”,0,round({Total Cash Value Now}/1.05^(DAYS({Due Date},TODAY())/360),0))

The statement ({Due Date}=“”) is true and when I make the following simple case it does not cause an error:

if({Due Date}=“”,0,1) returns 0.

When I substitute the 1 in the above IF function with the formula I want in case there is a date. round({Total Cash Value Now}/1.05^(DAYS({Due Date},TODAY())/360),0) I get the error when there is no date. Monday should not process that part of the IF function if it doesn’t need to go there.

2

Hi Johannes,

Indeed. This is called an eager approach of the IF statement vs a lazy approach. I actually wrote an article on this last week: A Simple Way to Handle Empty Columns and Avoid Errors in your Formulas | mdBoosters Blog.

Now if you don’t want to use a 3rd-party app, you’ll need to take a different approach than the one you took. You need to replace:

DAYS({Due Date},TODAY())

By this for instance:

DAYS(IF({Starting Date}="",TODAY(),{Starting Date}),TODAY())

This will generate 0 in your columns with no date. If you want the column to be empty, then you would need to replace it by:

IF(DAYS(IF({Starting Date}="",TODAY(),{Starting Date}),TODAY())=0,"",DAYS(IF({Starting Date}="",TODAY(),{Starting Date}),TODAY()))

With the rest of the formula around it, it is barely manageable, but it should work.

Want to use your formula results in automations, widgets, calendars? It is now possible with the Advanced Formula Booster. This 3rd-party app allows you to create formulas that write to any type of column, breaking free of the Formula column.

3

Hey Gilles,

Oh I did not know that there is a name for evaluating both outcomes in an IF function. I assumed this is an error in the design of the function. It appears there might be a purpose for it. Even though i lack the imagination about the usefulness of the eager approach. But now I have something to research. Thanks.

I subsequently asked monday.com for support as well and they ended up telling me that the IF function throws errors when one uses operators such as (^±*/) on emtpy cells . They recommended to use the functions instead. So I turned the formula into this and then it seemed to work.

round(if({Due Date}=“”,0,Divide({Total Cash Value Now},Power(1.05,DIVIDE(DAYS({Due Date},TODAY()),360)))),0)

Thanks again.