SOLVED

Non-developer here, how do I write SharePoint Calculations for:

Occasional Contributor
  1. I am counting number of days a project is On Hold (a yes/no column) with calculated column:
    Hold Days =DATEDIF([Hold Begin Date],TODAY(),"d")
    My question - When project is not On Hold, I want "d" to display "0" 
  2. Continuing the story above, when the Hold End Date is selected the Hold Days should only display the total days between Hold Begin Date and Hold End Date (right now it still accumulates days). 

Some background, I'm a writer rarely an excel user or formula developer. Please, explain what to do and why so I learn; looks like I may be "writing" more formulas (or begging for your help!).

Thank you so, so much!

 

2 Replies
best response confirmed by Aqua-holic (Occasional Contributor)
Solution

@Aqua-holic 

 

You could try below formula:

 

=IF(AND([Hold End Date]>0,[Hold Begin Date]>0),DATEDIF([Hold Begin Date],[Hold End Date],"d"),IF([Hold Begin Date]>0,DATEDIF([Hold Begin Date],TODAY(),"d"),"0"))

 

Make sure that you are using correct internal name of both the column in formula. Get hint from this link if you don't know how to find internal name of the column

 

Official Documentation: https://docs.microsoft.com/en-us/previous-versions/office/developer/sharepoint-2010/bb862071(v=offic... 

 

 

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 Thank you not only for the solution, but also the helpful hints.