 SOLVED

# Add to an existing formula, how to return "0" in Calculated Column when column is empty

Occasional Contributor

# Add to an existing formula, how to return "0" in Calculated Column when column is empty

I have this formula that returns positive and negative values correctly (returns a positive or negative number of days between 2 (estimated) dates).

I wish to add a caveat that if a date column is empty it should return a "0". Currently using just my formula below, if a date column is empty it returns 44,###. I know that the formula to return "0" is: IF([ColumnB]>0,DATEDIF([ColumnA],[ColumnB],"d"),"0") but I don't know how/where to add this to the below formula. Can someone help please? TIA!!

=IF(ISERROR(DATEDIF([ColumnA],[ColumnB],"d")),-DATEDIF([ColumnB],[ColumnA],"d"),DATEDIF([ColumnA],[ColumnB],"d"))

5 Replies
best response confirmed by Aqua-holic (Occasional Contributor)
Solution

# Re: Add to an existing formula, how to return "0" in Calculated Column when column is empt

You should first check whether any one of the column is empty and if it's empty then show 0, then you should check which date is bigger and based on date you should find out difference. In below formula I have considered ColumnB as latest date that means if ColumnA date will be greated than ColumnB then it will so day different in negative.

``=IF(OR(ISBLANK([ColumnA]),ISBLANK([ColumnB])),"0",IF([ColumnA] > [ColumnB],-DATEDIF([ColumnB],[ColumnA],"d"),DATEDIF([ColumnA],[ColumnB],"d")))``

Below is the output of above formula: Official Documentation: Calculated Field Formulas

Hope it will helpful to you and if so then Please mark my response as Best Response & Like to help others in this community

# Re: Add to an existing formula, how to return "0" in Calculated Column when column is empt

@kalpeshvaghela Calculated column formula works with display name of columns & not internal names.

@Aqua-holic Make sure you are using correct display name (name you see in list view - column header) of columns in formula.

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

# Re: Add to an existing formula, how to return "0" in Calculated Column when column is empt

Thanks for pointing it out

# Re: Add to an existing formula, how to return "0" in Calculated Column when column is empt

@ganeshsanap Yes, I use display name of the columns. Thanks! Have a great day!

# Re: Add to an existing formula, how to return "0" in Calculated Column when column is empt

@kalpeshvaghela - Thank you for your detailed response. I truly appreciate your help and kindness.