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

[Aqua-holic]

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"))

[#2 · Accepted solution]

@Aqua-holic 

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

[#3]

@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.

[#4]

@ganeshsanap 

Thanks for pointing it out

[#5]

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

[#6]

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