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

  • 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?

    Thanks in advanced. 

    Friday, November 23, 2012 8:08 PM

Answers

  • 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

    http://msdn.microsoft.com/en-us/library/bb862071.aspx


    /blog twttr @esjord

    • Marked as answer by efebo 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 Qiao WeiModerator Thursday, December 6, 2012 8:03 AM
    • Unmarked as answer by efebo Thursday, December 6, 2012 12:13 PM
    • Marked as answer by efebo Thursday, December 6, 2012 12:13 PM
    Wednesday, November 28, 2012 7:49 AM
    Moderator
  • 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
    Moderator

All replies