# How can I calculate the number of days between two dates? For example, DueDate vs Today.

• ### Question

• Hi,

I need to know the number of days between two dates. Basically, I have an expired task and I want to know how many days have passed from its Due Date. For example:

I have an expired task and its due date was 20/11/2012. Today (23/11/12) the task expired 3 days ago.

Can I create a condition to calculate this number only if the task’s status is not equal to completed?

Can I use a calculated column?

Friday, November 23, 2012 8:08 PM

• Using [Today] in a calculated column is pointless because the value get updated when the item is updated. In that way is it the same as the modified column.

You get the number of days between two dates with the formula

=DATEDIF([Column1], [Column2],"d")

refer below link for more Calculated Field Formulas

/blog twttr @esjord

• Marked as answer by Thursday, December 6, 2012 12:13 PM
Sunday, November 25, 2012 5:56 PM
• Hi,

Calculated column is not working in this scenario, as calculated column is only updated when an item is created or updated.

In this case, we should have something to be run daily, and when the current day pass the due date, update the expiration days.
The solution come to my mind is "Retention Policy" + "Workflow":

1. Create a new DateTime column in the Tasks list. Let's say "LastRunDay"
2. Create another Integer column in the Tasks list. Let's say "ExpirationDays"
3. Create a workflow for Tasks list. In the workflow, update the "LastRunDay" to today. Calculate the expiration days, and update to "ExpirationDays"
4. Go to Library Settings of Tasks list
5. Go to "Information management policy settins"
6. Select a content type
7. Enable Retention
8. Add a retention stage: "This stage is based off a date property on the item": Due Date + 1 days; Action:Start a workflow
9. Add a retention stage again: "This stage is based off a date property on the item": LastRunDay + 1 days; Action:Start a workflow
10. Go to Central Administration, Monitoring, edit timer job "Information management policy" and "Expiration policy"'s interval

Here are two articles that may help you to understand the solution well too:
HOW TO: Leverage SharePoint 2010′s Information Management Policies to trigger daily monitoring workflows… : http://vogtland.ws/markedwardvogt/?p=750
Workflow: Reminder Before Due Date – MOSS vs. WSS: http://www.sharepoint911.com/blogs/laura/Lists/Posts/Post.aspx?List=daba3a3b%2Dc338%2D41d8%2Dbf52%2Dcd897d000cf3&ID=64&Web=dbb90e85%2Db54c%2D49f4%2D8e97%2D6d8258116ca0

Thanks,
Jinchun Chen

Jinchun Chen(JC)
TechNet Community Support

• Marked as answer by Thursday, December 6, 2012 8:03 AM
• Unmarked as answer by Thursday, December 6, 2012 12:13 PM
• Marked as answer by Thursday, December 6, 2012 12:13 PM
Wednesday, November 28, 2012 7:49 AM
• Another solution is customizing Timer job:
http://www.codeproject.com/Articles/403323/SharePoint-2010-Create-Custom-Timer-Jobs

Jinchun Chen(JC)
TechNet Community Support

Wednesday, November 28, 2012 7:51 AM