locked
Is there a way to calculate a date field based on aother date field in sharepoint list? RRS feed

  • Question

  • In a SharePoint list, I have a 'Start of Use Date' and need to ensure the 'Review Date' is no more than 14 months after the 'Start of Use Date', but can't figure out the formula.  Can you help?
    Wednesday, January 8, 2014 7:18 PM

Answers

  • You can cast the field values in the SPListItem to DateTime objects and call the AddMonths(int) function to see if the two are more than 14 months apart. For instance:

    DateTime startOfUseDate = (DateTime) item["Start of Use Date"];
    DateTime reviewDate = (DateTime) item["Review Date"];
    if (startOfUseDate.AddMonths(14) >= reviewDate)
    {
       // Review Date IS NOT more than 14 months after Start of Use Date
    }
    else
    {
       // Review Date IS more than 14 months after Start of Use Date
    }


    Danny Jessee
    MCPD - SharePoint Developer 2010
    MCTS - SharePoint 2010, Configuring
    dannyjessee.com/blog

    Wednesday, January 8, 2014 7:33 PM
  • If you're using SharePoint 2010, and if it would be acceptable to treat a month as 30 days, you can just add the following formula to the list validation settings:

    =[Review Date]<=[Start of Use Date]+(14*30)
    If you're stuck using SharePoint 2007 or earlier, you can use the logic Danny Jessee mentioned in some event handler code to prevent the update from taking place.

    Wednesday, January 8, 2014 10:04 PM

All replies

  • You can cast the field values in the SPListItem to DateTime objects and call the AddMonths(int) function to see if the two are more than 14 months apart. For instance:

    DateTime startOfUseDate = (DateTime) item["Start of Use Date"];
    DateTime reviewDate = (DateTime) item["Review Date"];
    if (startOfUseDate.AddMonths(14) >= reviewDate)
    {
       // Review Date IS NOT more than 14 months after Start of Use Date
    }
    else
    {
       // Review Date IS more than 14 months after Start of Use Date
    }


    Danny Jessee
    MCPD - SharePoint Developer 2010
    MCTS - SharePoint 2010, Configuring
    dannyjessee.com/blog

    Wednesday, January 8, 2014 7:33 PM
  • If you're using SharePoint 2010, and if it would be acceptable to treat a month as 30 days, you can just add the following formula to the list validation settings:

    =[Review Date]<=[Start of Use Date]+(14*30)
    If you're stuck using SharePoint 2007 or earlier, you can use the logic Danny Jessee mentioned in some event handler code to prevent the update from taking place.

    Wednesday, January 8, 2014 10:04 PM