Removing ! (One of the Parameters is Invalid) In Formula Column

Platform discussions Special workflows & use cases
,
1

Hello All!

We were asked for a column that shows the # of Months between Inception Date and ToB Date and I was able to figure that out with this formula:

IF({ToB Date}=“”,“0 Days”,ROUND(YEARFRAC({Incept Date},{ToB Date}) * 12,0))

However, when one of the dates is blank, the formula column returns with this:
Screenshot 2024-10-31 at 12.36.05 PM

How can I make it so that when one of the dates is blank it return a blank instead of the !.

Thank you!

2

Hi Grace,

Try:

IF(OR({Incept Date}="",{ToB Date}=""),"",
  ROUND(YEARFRAC(
    IF({Incept Date}<>"",{Incept Date},"2000-01-01"),
    IF({ToB Date}<>"",{ToB Date},"2000-01-01"))
    *12,0
  )
)

Note that the 2000-01-01 date could be anything. It does not matter because it is never taken into account, but it prevents the YEARFRAC function from failing.
 
 

What if we could break free of the Formula column? and write formulas that update any type of columns? What if a formula could update multiple columns at once? This is possible with the Advanced Formula Booster app.

3

Thank you so so much! That worked perfectly.