Help with Formula to Return Blank for Empty Column

Platform discussions
,
1

Hello everyone,

I have set up the following formula:

IF(YEAR({FY End}) < YEAR(TODAY()), YEAR(TODAY()) - YEAR({FY End}) + 1

IF(YEAR({FY End}) = YEAR(TODAY()), “1”

IF(YEAR(TODAY()) < YEAR({FY End}), “0”

These conditions are working well, but I would like to modify the formula to return a blank result when the {FY End} column is left blank. However, currently, it shows a “!” instead of remaining blank.
Could anyone kindly help me in adjust the formula so that if {FY End} is blank, the result will also be blank?

Formula Error
Formula Error136×287 3.46 KB

Formula Type

2

Try this:

IF({FY End}="", "", IF(YEAR({FY End}) < YEAR(TODAY()), YEAR(TODAY()) - YEAR({FY End}) + 1, IF(YEAR({FY End}) = YEAR(TODAY()), "1", IF(YEAR(TODAY()) < YEAR({FY End}), "0", ""))))
1 Like
3

Hi @Jit02,

The problem is that YEAR({FY End}) throws an error if {FY End} is empty.

The solution suggested by David doesn’t work, because monday uses the Eager approach when calculating the IF statement he suggests (if you really want to understand what this means, see A Simple Way to Handle Empty Columns and Avoid Errors in your Formulas).

So, to avoid getting errors, you need to replace all occurrences of YEAR({FY End}) by IF({FY End}=“”,“1900-01-01”,{FY End}). This is a bit ugly, but this way, the YEAR function is always provided a date and won’t fail.

Your syntax becomes:

IF(YEAR(IF({FY End}="","1900-01-01",{FY End})) < YEAR(TODAY()), 
    YEAR(TODAY()) - YEAR(IF({FY End}="","1900-01-01",{FY End})) + 1, 
        IF(YEAR(IF({FY End}="","1900-01-01",{FY End})) = YEAR(TODAY()), "1", 
        IF(YEAR(TODAY()) < YEAR(IF({FY End}="","1900-01-01",{FY End})), "0", "-")
    )
)

There is one more problem to resolve though.

When your {FY End} is empty, you won’t get the desired “-”, but 125 right now because we are in 2024, i.e. the difference between 1900 and 2024. Next year, you’ll get 126, etc. So you need to modify the 2nd line above and test if {FY End} is empty.

Here is the final solution:

IF(YEAR(IF({FY End}="","1900-01-01",{FY End})) < YEAR(TODAY()), 
    IF({FY End}="","-",YEAR(TODAY()) - YEAR(IF({FY End}="","1900-01-01",{FY End})) + 1),
        IF(YEAR(IF({FY End}="","1900-01-01",{FY End})) = YEAR(TODAY()), "1", 
        IF(YEAR(TODAY()) < YEAR(IF({FY End}="","1900-01-01",{FY End})), "0", "-")
    )
)

I used 1900-01-01, but you could use any date really.

Now, if you’re serious about formulas and don’t want to deal with these issues anymore as well as get many more features, you may want to use a 3rd-party app called the Advanced Formula Booster.

Here is how you would handle this situation with the Advanced Formula Booster.

2024-12-29_07-34-28
2024-12-29_07-34-28488×298 70.8 KB

As you can see with the app, you can use as many lines as you want in your formulas, you can use comments, variables to store values that you can reuse later (anything between [ ] is a variable) and you can write to any column (it doesn’t use the formula column). In this case, I created a {Result} text column to hold the result of the formula.

Hope it helps.

1 Like
4

Thanks @GCavin for the much more detailed response. I was going to link your blog post in my earlier answer to explain why it’s a bit tricky to achieve the result @Jit02 wanted but I couldn’t find the link. But yeah I agree with all you’ve said. There’s just some things that are a bit tricky to achieve with the formula column.

@Jit02 the monday native column is nice but it’s very limited in a lot of areas, for example you can’t project the results of the formula column to other column types, or you can’t use the formula column results in automations. For that you will need a 3rd party app which allows you to do a lot more and then some with formulas.

You can project formula results to mirror columns, item names, more columns than Monday native formula column supports, have access to much more powerful functions.

I hope that helps.

Cheers.

1 Like
5

Hola Jit02!

Espero pueda ayudarte esta configuración de la Fórmula.
En mi caso necesitaba tomar dos fechas relevantes y luego condicionar por periodos, esto fue aplicado a un tablero de vacaciones, para que te puedas dar una idea…
Ojala te pueda servir!
Que tengas un lindo dia!
Saludos!

Fórmula:
IF(DAYS(TODAY(), {FECHA RECONOCIDA}) <= MULTIPLY(5, 365), 14, IF(AND(DAYS(TODAY(), {FECHA RECONOCIDA}) > MULTIPLY(5, 365), DAYS(TODAY(), {FECHA RECONOCIDA}) <= MULTIPLY(10, 365)), 21, IF(AND(DAYS(TODAY(), {FECHA RECONOCIDA}) > MULTIPLY(10, 365), DAYS(TODAY(), {FECHA RECONOCIDA}) <= MULTIPLY(20, 365)), 28, IF(DAYS(TODAY(), {FECHA RECONOCIDA}) > MULTIPLY(20, 365), 35, " "))))

Tablero:

image
image462×269 12.5 KB

6

@Jit02 To adjust your formula so that it returns a blank result when the {FY End} column is empty, you can use the IF function to check if {FY End} is blank (""). Here’s the modified formula:

IF(EMPTY({FY End}), “”,
IF(YEAR({FY End}) < YEAR(TODAY()), YEAR(TODAY()) - YEAR({FY End}) + 1,
IF(YEAR({FY End}) = YEAR(TODAY()), “1”,
IF(YEAR(TODAY()) < YEAR({FY End}), “0”, “”))))


Explanation:
1. **`IF(EMPTY({FY End}), "", ...)`:** 
   - This checks if `{FY End}` is empty. If it is, the formula returns a blank (`""`).
   - If not, the rest of the formula is executed.

2. The rest of the formula remains unchanged:
   - Compares the year of `{FY End}` to `YEAR(TODAY())` and evaluates accordingly.

By using the `EMPTY` function, the formula avoids returning a `!` when `{FY End}` is blank.