Forum Discussion
Add to an existing formula, how to return "0" in Calculated Column when column is empty
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
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
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.
Thanks for pointing it out- ganeshsanap Yes, I use display name of the columns. Thanks! Have a great day!
- kalpeshvaghela - Thank you for your detailed response. I truly appreciate your help and kindness.
