I am working with a SP 2010 list. The list is of systems that require an annual inspection. Users are able to enter their inspections to an SP Library, through an InfoPath form. That process sends me an alert that new inspections have been uploaded.

At that point I open the SP 2010 list. The list has columns that include 'Date of Inspection', 'Date of Next Inspection', 'Compliance'. I am currently performing the calculations outside of SP, using and excel spreadsheet, and then cutting and pasting that information back to the SP 2010 list, which queries the data and displays it on a user InfoPath form that allows the user to know whether a system is 'Compliant' or 'Noncompliant' with the annual inspection requirement, based on the current date.

I am looking for a way to perform the calculations in the SP 2010 list, rather than in the excel spreadsheet. Is there a process that would allow me to have an SP 2010 list perform and display the compliance calculation, rather than having to export/import the data in and out for the calculation?

  • Is all of the data needed for the calculation in the list? If so, you can just use calculated fields. – Erin L Oct 17 '16 at 21:06
  • Compliance is based off a comparison of the date of last inspection compared to 'Today'. The device is noncompliant if not inspected every 365 days. All indications are the current date ('Today') cannot be used in a calculated field. I am looking for a way to have the database list look at each line item 'Date of Last Inspection' field every 24 hours; and, where the it is more than 364 days since the last inspection the database changes the status field from 'Compliant' to 'Noncompliant'. – Curtis Oct 19 '16 at 1:50

You should be able to use a calculated field. In this case, you want to say "If Today minus Date of Last Inspection is greater than 364, then Status equals Noncompliant, else Status equals Compliant. You can see the syntax and available calculations here.

Create a calculated field called Status and set the formula to:

=IF([Today]-[Date of Last Inspection]>=364,"Noncompliant","Compliant")
  • Thank you for your attention to this matter. I have read all over the intersweb that Today and Me cannot be used in SP list calculated columns. When I try your formula I get an error message that states "Calculated columns cannot contain volatile functions like Today and Me." Please advise. – Curtis Nov 28 '16 at 22:05
  • I forgot that. There's a workaround. Create a column named Today, create your calculated field with the formula I provided, then delete Today column. You'll have to recreate the fake column in the future if you want to change this formula. – Erin L Nov 29 '16 at 13:45
  • Nope, still not working. – Curtis Nov 29 '16 at 22:12
  • Nope, still not working. I have a column for: Date of Last Inspection (Date Only type column); Today (Date Only type with Today's Date button chosen); Status (Calculated Column with =IF(Today-[Date of Last Inspection]>=364,"Noncompliant","Compliant"). All that gets me "Calculated columns cannot contain volatile functions like Today and Me". Please advise, thank you. – Curtis Nov 29 '16 at 22:23
  • Trying putting the square brackets around [Today] – Erin L Nov 29 '16 at 23:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.