SharePoint Online List - days between two date columns

Question

I've got a very simple formula to calculate the number of days between two dates which works, with one exception.

Formula:

=IF(ISBLANK([Completed Date]),"TBC",[Completed Date]-[Created Date])

When the 'Completed Date' value is empty I don't get 'TBC', instead SharePoint displays: #VALUE!

Can someone please point me in the right direction?

Answer 1

According to my test, there is nothing wrong with the formula you provided.

You can use the following formula instead:

=IF([Completed Date]="","TBC",DATEDIF([Created Date],[Completed Date],"d"))

------------------------------------- OR ----------------------------------------

=IF([Completed Date]="","TBC",DATEDIF([Created Date],[Completed Date],"d"))

Answer 2

Syntax in your formula seems correct & it also works at my end.

Example:

---

Try below combinations & see which one works for you:

=IF([Completed Date] = "", "TBC", [Completed Date]-[Created Date])

OR

=IF(LEN([Completed Date])=0, "TBC", [Completed Date]-[Created Date])

Above two formula returns same results as shown in above image.

Documentation:

1. Calculated Field Formulas
2. Examples of common formulas in lists