Survey, validate today's date question

Question

We have a private survey. The date the survey was completed is to appear in an Exported Excel sheet.

To do so, I have added a Date Field question in the survey named something like "Today's date" and the answer is populated with the current date.

My question is: How do I validate that the date hasn't been altered to make sure that nobody puts a past date to cover their as$es because they forgot?

I wanted to add an OR condition in the Validations such as :

OR([Date]>NOW(),[Date]<NOW())

But SharePoint is giving me a formula error. I've tried at the list level and at the question's level.

Edit: We DON'T have access to SharePoint Designer.

Answer 1

I believe what you need is to pull Modified date into the Excel, since it effectively shows the exact date when Survey was completed. And there's no way for a user to change it.

It can be done pretty easily with SharePoint Designer.

Open up SharePoint Designer, navigate to your Survey list, and click "Overview" under Views box, find <ViewFields> in the editor, and add the Modified field there. Like this:

Now, you can export again or even just refresh existing spreadsheet. Here's what you'll get:

The only thing left is to change the format of the Modified column into Date format, which is a very simple thing to do and I believe everyone can do it (because obviously that has to be done each time the data is exported).

And that's it, you're done!

Update: Without SharePoint Designer

If you don't have access to SPD, you can achieve exactly the same result using JSOM.

First, you'll need IDs of the list and the view.

There's an easy way to get those (any alternative way also will do). When you export to Excel, you're downloading file called owssvr.iqy. Instead of opening this file in Excel, open it in a Notepad or other text editor. You'll see something like this:

Copy out the GUIDs - ListView & ListName. Yep, ListName is a funny name for guid value, but in SharePoint it's OK :) Now, open you portal, then open your browser console (F12 in IE), copy-paste there the following script, replace PUT-LIST-GUID-HERE and PUT-VIEW-GUID-HERE with actual guids, and execute!

var ctx = SP.ClientContext.get_current();
var list = ctx.get_web().get_lists().getById('{PUT-LIST-GUID-HERE}');
var view = list.get_views().getById("{PUT-VIEW-GUID-HERE}");
var fieldsInView = view.get_viewFields();
fieldsInView.add("Modified");
view.update();
ctx.executeQueryAsync(function()
{
  alert('SUCCESS!');
},
function()
{
  alert('ERROR');
});

I tested this approach and got the SUCCESS alert, and after that Modified field appeared in the Excel after refresh.