InfoPath Dev

 I have a SP list of systems. The systems require annual assessment. The list includes a column for 'Date of Last Inspection' and 'Compliance Status'. I am currently using formulas in an Excel spreadsheet to calculate compliance ('Compliant' or 'Noncompliant'), based on 'Date of Last Inspection', 'Date of Last Plus 1 Year', and the 'Current Date'. Once calculations are complete I cut and paste the updated data into the SP list. Is there a way, using calculated columns, and other appropriately configured columns with values, to achieve the same results; eliminating the need for the Excel spreadsheet and the cut and paste? 

 I wonder about custom workflow for this - do you have access to SharePoint Designer & permissions that would allow you to create a workflow? I know there is a pause step, so you could start a workflow and pause till.... Date of Last Plus 1 Year, I suppose. I don't know - having a workflow active for a year seems a little extreme, but is this a big list with lots of items?

 There are about 1900 systems in the list. Most inspection reports come in one at a time. Though, there are times when one unit will send in dozens at a time. I'd like:

1. To have a running 'clock' on current entries in the list, and when the difference between 'Today's Date' and the 'Date of Last Inspection' exceeds 364 days have SP change the 'Status' column text automatically from 'Compliant' to 'Noncompliant'. I, currently, have to do this in an Excel spreadsheet and cut and paste the results into the SP list. For single reports, I can obviously manually update the Compliance column as necessary. However, for dozens, having a series of calculated columns and fields do this is preferred.

On a second issue; I have several InfoPath inspection deficiency upload. Each form has a workflow associated with it that sends me and email every time one is uploaded. There is a corresponding field assessment form. The field form has dozens of questions. The upload form only has the ability to enter information on 10 potential deficiencies form the field form, not all of the questions. Currently the workflow works great. The workflow email provides data on the first seven or so lines, which are always populated with general site information data - that part is fine. Its the deficiency lines that are the issue. Each deficiency has three lines of information; 1. Deficiency Question No; 2. Deficiency Question (auto-populated based on user manually entering Deficiency Question No); and Deficiency Comment (required to be manually enter when a Deficiency No is entered). Because there are ten (10) opportunities to enter deficiency comments, many of the emails have a lot of blank labels. Is there a workflow variable that can be created that tells the SP workflow to only gather and display in the email fields that have text within, leaving out all fields that are blank?   

 I think without some kind of code you won't be able to have a running "clock". Workflow with a pause step is the best suggestion I have for this. Is your email sent via workflow or from the form? If the form is using an email submit connection to send the email, you could do some formatting to hide blanks when the email sends. If you are using SharePoint Workflow to send the emails, you would need to modify your workflow to likely send a different email based on the values that are populated - or maybe build up the email text in a workflow variable using conditions to add to the email text (sorry, I'm being vague on that because I've not tried it and would have to try it know what would work!)

Victory!!!

I changed the label of the 'Today' column to 'Current', and I set the Default Value to 'Today'. This allowed me to use the following formula in the calculated field Compliance Status column: =IF(Current-[Date of Last Inspection]<=365,"Compliant","Noncompliant").

It took  two weeks, but I finally got it. Thanks for your assistance. 

Now that I've got this working, another issue has come out of that success. When I was doing this calculation through Excel, in the event no report was submitted, I would simply manually enter 'No Report Submitted' in the SP list column of 'Date of Last Inspected' (remembering at the time there were no SP calculations taking place).

Now that there is a SP calculated field involved, if I type any text into the 'Date of Last Inspected' column, the process will see this as a error and generate '#VALUE' in the Compliance column, rather than an indication of 'Compliant' or 'Noncompliant'. Is there a way to edit the 'now' functioning IF statement below to include the function of determining if the 'Date of Last Inspection' field is blank, and if so make the Compliance column 'Noncompliant', and if not text complete original calculation as shown below?

 =IF(Current-[Date of Last Inspection]<=365,"Compliant","Noncompliant").