Sharepoint Date Difference

Question

I have a problem in Sharepoint. I have a list with two columns, [Start date] and [End date]. I've made a new column, a calculated field, in which I want to see the difference number of days between the two dates, like 15(days only). The problem is that [End date] could be empty, so this new column should be not empty but should calculate then todays date - start date and should automatically update daily till the end date is not entered.Any help will be highly appreciated.Thanks in advance.

Answer 1

You can use the following formula for the calculated field:

 =IF(ISBLANK(enddate),(Today-startdate),(enddate-startdate))

the above calculates based on the current date when the item is being updated.Calculated fields don't get updated until the item itself is updated. The following links might help you with your requirement to update the fields everyday.

http://blog.pathtosharepoint.com/2008/08/14/calculated-columns-the-useless-today-trick/

Answer 2

A few months ago I faced with the same problem: updating calculated columns with 'TODAY' (and differences in dates). The main problem: SharePoint does not recalculate values until the item is changed. IMHO, it is not good idea to update (change) all items of the list to recalculate fields.
I found a way to force SharePoint to update calculated columns I wanted (without updating each item of the list):

Update calculated field with PowerShell script

I update calculated list fields every morning (by schedule) with PowerShell script (so every day I have actual values):

$calculatedField.Update()

Executing of the code lasts only 10-20 seconds (list with thousands records and 3 calculated columns). This doesn't cause changes to item (does not affect the date of the last changes).

---

Full text of my script:

Add-PSSnapin Microsoft.SharePoint.PowerShell
$webURL = "http://your.site"
$web = get-spweb($webURL)

# I have 3 calculated columns, so I prefer to use an array:
[string[]] $arrFieldName = @("ExecDateFact", "ExecDateDiff", "Substatus")
# get list:
$list = $web.GetList($web.url+"/Lists/" + "your_list_name")
# get fields of the list:
$listFields = $list.Fields

# update each calculated field:
foreach ($fldName in $arrFieldName) {
    $listFields.GetFieldByInternalName($fldName).Update()
}
# release resources:
$web.Dispose()

I haven't seen this approach anywhere, so I have written this answer with hope this will help somebody.