1

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?

0

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

enter image description here

You can use the following formula instead:

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

enter image description here

enter image description here

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

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

enter image description here

enter image description here

1
  • Thanks very much, not sure why my formula works for you and not me, but your alternate suggestion worked for me, so thanks! Oct 18 at 15:50
0

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

Example:

enter image description here


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.