I have a very simple formula to calculate the number of days between two dates in a Sharepoint list =[Action Closed]-[Action Assigned]. It returns the desired result when both columns are populated, however, if [Action Closed] has no value the returned result is shown as a number above -42,000.
What is the formula to show the result as blank when [Action Closed] is blank?
It sounds like you're looking for:
=IF(ISBLANK([Action Closed]), "", <<Your current calculated column formula>>)
Note that IF takes 3 parameters: an expression that evaluates to true or false, what to display if the evaluation is true, and what to display if the evaluation is false. You should supply your entire current calculated formula for the false-case parameter.
Your specific example would look like this:
=IF(ISBLANK([Action Closed]), "", [Action Closed]-[Action Assigned])
Note that we provide the empty string ("") for the true parameter, because we're saying if [Action Closed] is blank, then provide what appears to be no value within this column -- this is how we show a calculated field as blank like your question originally asked.
I also strongly encourage anyone looking for more information about calculated columns to check out the MSDN reference at: https://msdn.microsoft.com/en-us/library/office/bb862071(v=office.14).aspx
You get a -42 thousandsomething number because a blank date defaults to the oldest past date SharePoint can represent, which is 31-21-1899 (or 1900?)
So like the others answered you have to test for a blank value with:
=IF( ISBLANK([Action Closed]) , "Action not closed" , [Action Closed]-[Action Assigned] )
The MSDN reference is not always correct, see the SharePoint Functions and Syntax List I recently compiled.
If you are editting Calculated Formulas my CalcMaster Bookmarklet is of great help also, as it gives you immediate feedback while editting.
Susanne, its easier to use a plain text editor when writing Formulas, then paste them in SharePoint. SharePoint will ignore all new lines so you can copy/paste Formulas like":
=IF( ISBLANK( [Action Closed] )
, "Action not closed!!"
, IF( ISBLANK( [Action Assigned] )
, "Action Closed but not assigned!!"
, "Action took:" & [Action Closed]-[Action Assigned] & " days"
)
)
This shows how you can nest functions, but you have to be very precise with all commas and brackets!
The CalcMaster Bookmarklet I made public would really help you out, but you may have to ask a SharePoint power-user to help you install it.
Another tip is to built them in Excel, as you get immediate results in an Excel sheet. Just be aware not all Excel functions work in SharePoint.
The above Formula can be tested in Excel. So get it right in Excel first. Replace the Column names with Excel cell references like:
=IF( ISBLANK( A1 )
, "Action not closed!!"
, IF( ISBLANK( A2 )
, "Action Closed but not assigned!!"
, A2-A1
)
)
If you don't know how to write Excel functions, then learn them first. Plenty of online courses around.
SharePoint (Formulas) is like learning to ride a bicycle without side-wheels, but its worth taking some falls, getting up and persevering.
And learn the Markup to use on StackOverflow
Well formatted and detailed questions get you better answers. And marking answers as Answer also gain you more credit.
I think you can add IF in your formula. If [Action Closed] has no value, show blank, if [Action Closed] has value, show calculated number.
