Display blank if column is empty

Platform discussions Special workflows & use cases
1

Hello,

I’ve been looking all over this platform as well as trying out stuff, but I can’t figure out this formula.

Right now I have the following formula that displays the age in years and months :

INT(YEARFRAC(FORMAT_DATE({Date of Birth}),FORMAT_DATE(TODAY()))) & " years “& INT(MOD(YEARFRAC(FORMAT_DATE({Date of Birth}),FORMAT_DATE(TODAY())), 1) * 12) & " months”

I want it so that if the Date of Birth column is empty, then the formula is blank instead of showing the exclamation mark.

I tried:

IF({Date of Birth},(INT(YEARFRAC(FORMAT_DATE({Date of Birth}),FORMAT_DATE(TODAY()))) & " years “& INT(MOD(YEARFRAC(FORMAT_DATE({Date of Birth}),FORMAT_DATE(TODAY())), 1) * 12) & " months”),“”)

But it still shows up with the exclamation. Any help is greatly appreciated. Thank you :slight_smile:

2

Hi Marie-Ève,

Try:

IF({Date of Birth}<>"",Your_original_formula,"")

Or

IF({Date of Birth}="","",Your_original_formula)
3

Hi Gilles! Thanks for your quick reply.

With both solutions, I remain with an exclamation mark if the DOB is blank.

However, what’s weird is that with the first option, the > isn’t blue but white, so it looks like the formula is using it as an actual character and not as part of the formula ?

In this topic, I had found that if I ONLY write down what Jim said at the end, it shows up empty and works fine :

IF({Date},INT(YEARFRAC({Date}, TODAY())),“”)

But as soon as I replace the formula above with my own, it doesn’t work anymore… Is maybe my formula wrong?

Thanks again for your help :slight_smile:

4

Peux-tu poster ta formule complète?

5

Bien sûr. Voici toutes les formules que j’ai essayées:

IF({DoB}<>“”,(INT(YEARFRAC(FORMAT_DATE({DoB}),FORMAT_DATE(TODAY()))) & " years “& INT(MOD(YEARFRAC(FORMAT_DATE({DoB}),FORMAT_DATE(TODAY())), 1) * 12) & " months”),“”)

IF({DoB},(INT(YEARFRAC(FORMAT_DATE({DoB}),FORMAT_DATE(TODAY()))) & " years “& INT(MOD(YEARFRAC(FORMAT_DATE({DoB}),FORMAT_DATE(TODAY())), 1) * 12) & " months”),“”)

IF({DoB}=“”,“”,(INT(YEARFRAC(FORMAT_DATE({DoB}),FORMAT_DATE(TODAY()))) & " years “& INT(MOD(YEARFRAC(FORMAT_DATE({DoB}),FORMAT_DATE(TODAY())), 1) * 12) & " months”))

Merci encore pour votre aide. :slight_smile:

6

I can’t find a solution, sorry. Maybe someone else has one.

There are 2 things that make the formula break.

  1. Contrarily to one might think, when you write:
IF(Condition,OptionA,OptionB)

monday will calculate OptionA, then OptionB and return the appropriate one based on the condition. One would think that it would first evaluate the condition, then calculate only the appropriate option: that’s not the case.

  1. Once you know that, the problem is with the * 12. When there is no date provided, you end up multiplying NULL * 12 which generates an error and the infamous red exclamation mark. Try it without * 12 and you’ll see that it does work (and that’s also why the formula you found in the other topic does work!).

Maybe there is a solution using the formula column, I couldn’t find one. Anyone else?

Now, there is a solution without using the formula column, but it requires using an app called “Advanced Formula Booster”. With this app, you design your formulas outside of monday and it is a lot more intuitive. It is a different concept. For example, you can use as many lines as you want.

Here is what this same formula would look like targeting a {Age} text column(a regular text column).
2023-10-19_10-40-33

I am the main developer of this app which was released this week. I decided to develop it after spending hours answering formula questions here to finally get annoyed with the complexity of most solutions I would recommend.

Should you want to test it, you’ll find all the info at https://docs.mdboosters.com. There is a trial version to play with it.

Since we are just launching it, I can provide you with a discount once the trial is over. Don’t hesitate to ask.

2 Likes
7

Thank you for your feedback Gilles! I’ll wait to see if someone might have an answer, otherwise I might look into the app you mentioned.

Thanks again for taking the time to look into my question! :slight_smile:

8

@marieever

Gilles is correct…

To make your formula work, you have to make the parts work even when the DOB is blank.

Try this:

IF({DoB}, 
  INT(YEARFRAC(FORMAT_DATE(IF({DoB}, {DoB}, "1/1/1")),
    FORMAT_DATE(TODAY()))) & " years " & 
  INT(MOD(YEARFRAC(FORMAT_DATE(IF({DoB}, {DoB}, "1/1/1")), 
    FORMAT_DATE(TODAY())), 1) * 12) & " months", 
  ""
)

Jim - The Monday Man
Get Custom Apps, Integrations & Automations for monday

2 Likes
9

@JCorrell
I knew there had to be a way. You’re the man!

10

HI Jim!

Thank you so much for your help.

I tried the formula, but it seems to be partially working? The formula gives the age of people born in january, mars, september, october and november only. If someone is born in any of the other months, the formula column shows up with the infamous exclamation mark.

At least, when there is no date of birth, the formula column really shows up blank so that’s amazing!

Is that normal? Is there a workaround for this? Thank you again :slight_smile:

11

@marieever

I was not able to make it fail. To confirm, DoB is a date column? Also, can you give specific examples of dates that fail?

12

Hi Jim,

The DoB is indeed a date column. For example, the following dates all make the formula fail:

  • February 16th
  • April 2nd
  • May 23rd
  • June 1st
  • July 30th
  • August 8th
  • December 24th

These are only examples, because on my side, all dates in February, April, May, June, July, August and December fail.

Thanks again! :slight_smile:

13

@marieever

I am still not able to make it fail. I would suggest opening a ticket with support@monday.com.

Jim - The Monday Man
Get Custom Apps, Integrations & Automations for monday

14

Hi Marie-Eve, tested on my side the formula, and i have the same issue as you (some date fail like date in february, december…).
FIY, I have french date format… (24 Octobre, 2023). I guess it could be a clue.
Bonne journée

Joachim

15

@marieever, @Joachim

You might try this:

IF({DoB}, 
  INT(YEARFRAC(FORMAT_DATE(IF({DoB}, {DoB}, "1/1/1"), "YYYY-MM-DD"),
    FORMAT_DATE(TODAY(), "YYYY-MM-DD"))) & " years " & 
  INT(MOD(YEARFRAC(FORMAT_DATE(IF({DoB}, {DoB}, "1/1/1"), "YYYY-MM-DD"), 
    FORMAT_DATE(TODAY(), "YYYY-MM-DD")), 1) * 12) & " months", 
  ""
)
1 Like
16

You’re the man. it works fine now for me :wink:
Thanks

17

Hi Jim,

This works perfect now ! Thank you so much for your help, I appreciate it ! :slight_smile:

Have a great day,

1 Like