Show n/a in formula column if date is blank

Platform discussions Special workflows & use cases
, , ,
1

Hi I’m using the following formula to calculate the ages of the clients I work with; however I don’t have all of their DOB’s. I wanted to know if I could add something to this formula to either show “n/a” or leave the column blank if a date is not inputted instead of showing the error exclamation. Thanks!

INT(YEARFRAC(FORMAT_DATE({DOB}),FORMAT_DATE(TODAY()))) & " yrs " & INT(MOD(YEARFRAC(FORMAT_DATE({DOB}),FORMAT_DATE(TODAY())), 1) * 12) & " mo "

2

This can be solved by an IF function in the formula type column .
The formula will change to:
IF({DOB} = “”, “n/a”, INT(YEARFRAC(FORMAT_DATE({DOB}), FORMAT_DATE(TODAY()))) & " yrs " & INT(MOD(YEARFRAC(FORMAT_DATE({DOB}), FORMAT_DATE(TODAY())), 1) * 12) & " mo ")

3

Unfortunately, that didn’t work. It’s now showing the entire column as errors.

4

That might be due to fact of absent parenthesis in the internal formula
Try using this:

IF({DOB} = “”, “n/a”, ( INT(YEARFRAC(FORMAT_DATE({DOB}), FORMAT_DATE(TODAY()))) & " yrs " & INT(MOD(YEARFRAC(FORMAT_DATE({DOB}), FORMAT_DATE(TODAY())), 1) * 12) & " mo ") )

5

@mfink did you try the latest formula? Let me know

6

Hi—unfortunately that one didn’t work either. This time it retained the information from the original formula, but still shows the error icon for the rows with missing DOB’s.