Skip to main content

Restrict Future or Past Date in List

Many a times you will come across a requirement wherein you need to restrict user from entering future or past date in a List or Document Library Date column.

Developers are inclined towards writing either event handler or javascript to achieve this. But, hold on! You can achieve this without writing a single line of code.

I have a list which stores Employee details like Employee Number, Name and Date of Joining.


Here I need to enure that Date of Joining will not accept future date. Navigate to List Settings -> Validation Settings. Here you can enter the formula to restrict future date as shown below. If you change the logical operator, it will restrict past date. Also, you can put message which will be displayed to user.


Now, when you try to enter future date and try Save, you will see the validation message.

So, next time you have similar requirement, you know where to try first before jumping into coding!
Labels:

Comments

  1. UnknownDecember 18, 2015 at 3:15 AM

    please can you show me how to restrict date not to exceed November 2016

    ReplyDelete
  2. SureshDecember 30, 2015 at 7:41 PM

    Here is an example:
    =[DateColumn]<DATEVALUE("11/01/2016")

    ReplyDelete
  3. UnknownJanuary 17, 2016 at 11:12 AM

    Hi , can you please show me =<=Today and =>=Today in wss 3.0

    ReplyDelete
    Replies
    1. SureshJanuary 18, 2016 at 10:59 AM

      Bhuvanendran, what exactly is your question? Can you explain what exactly your requirement is?

      Delete
  4. UnknownJanuary 18, 2016 at 11:04 PM

    Suresh, Actually I have created one custom list in wss 3.0 for daily call log report. Here Reporting and closing time there are two field is there.
    Reporting Time <= Today () and Closing Time >=Today () should be like this and I have applied a formula with this two column as Total time taken =TEXT ([Closing Time]-[Reporting Time],"h: mm") (This formula is working correctly)
    Now the default option is we can select any date but thing is If we select future reporting date, error should come and if we select
    For example:
    Reporting Date & time should be today or past date is 18/1/2016 or 17/1/2016 and closing time is 18/1/2016 or greater (19/1/2016 ) and it should not be past date .
    This is my exact requirement. Can you suggest how to do in wss 3.0

    ReplyDelete
  5. SureshJanuary 19, 2016 at 12:54 PM

    Try this formula:
    =IF([Reporting Time]<=TODAY(),IF([Closing Time]>=TODAY(),TRUE,FALSE),FALSE)

    ReplyDelete
  6. UnknownJanuary 20, 2016 at 6:10 PM

    Suresh,I have tried this one but it's not taken and the error shows like syntax error or not support . for example i tried = Today is working but = Today() is not workling and i have checked with <, > symbol also not support i tried like <=Today ,>= Today . Is any other way to do it in wss 3.0 . Please

    ReplyDelete
    Replies
    1. SureshJanuary 21, 2016 at 1:02 PM

      A couple of things:

      1. I tried this formula myself before posting and it worked. You need to use this formula under List Validation (as explained in this blog post), and not Column Validation.

      2. I tried this formula in SP2013 and not in WSS 3.0.

      Can you confirm you tried as mentioned in point 1?

      Delete
    2. UnknownJanuary 21, 2016 at 4:35 PM

      No actually in wss3.0 there is no validation settings while creating the column there is an option like calculate value .Here only i tried . Let me know Is there any formula syntax is different for wss3.0 .

      Delete
    3. SureshJanuary 21, 2016 at 6:55 PM

      Ok, I don't remember WSS 3.0. If you even had column validation, you could have tried these formula under respective columns:

      =[Reporting Time]<=TODAY()

      =[Closing Time]>=TODAY()

      Calculate value is to derive default date based on a calculation. As far as I know, it cannot be used for validation.

      Delete
  7. JudyJuly 30, 2019 at 9:40 AM

    Can you advise how to tweak the formula for local time zone. I am in Eastern, but site is set up for Pacific. It is after midnight here and I tried to enter 7/30/19, but since that is a future date from the perspective of the Site zone of Pacific I got the error message I created to not allow future dates.

    ReplyDelete
    Replies
    1. SureshAugust 21, 2019 at 7:39 PM

      @Judy, I know I'm replying very late, sorry for that. Handling time zone in SharePoint is really a difficult task. It is due to the fact that SharePoint stores date in UTC format in database and converts date and time as per the regional setting.
      As far as I know, without writing a complex code, there is no way to handle within a calculated column or formula.

      Delete

Post a Comment

Popular posts from this blog

All about SharePoint List View Styles

Sometimes, there are out of the box features which we tend to ignore and later when we do apply, we are more than happy about the feature which is readily available in SharePoint. One such feature is List View Style. I never thought I would write a post on this. However, whenever I spoke about this with users, people were excited to see the result. That prompted me to write this post. Instead of getting into only theory part, I will basically take use cases where these styles can be applied and also touch up on on some minor limitations with certain style. When you are creating/modifying a List view, you will get an option to select View Style. As shown below, there are 8 options available and Default is always set if you ignore this style. List of View Styles I will take typical Contact List and Announcement List to explian about these styles. Let us go one by one. Default: This view, as name suggest, is the default style in a view. This is one of the widely seen style ...
1 comment
Read more

How to update Person field with multiple values using REST API

Person or Group field in SharePoint is similar to a Lookup field. When you are updating this field using REST API, you need to append "Id" to the name of the column in the body construct. For example, the body construct looks like this: data: { "__metadata": { "type": "SP.Data. ListName ListItem" }, "Title": "First Item", " PeopleField Id": "4" }; The highlighted portions should be replaced by the actual List Name and Column Name. In the above example, the REST call is updating a List item with Title and People columns. How to get the value for user ID ("4" in the above example) needs a separate explanation and that will be my next post! The above example works fine if Person field is configured to accept only one value. If we change the Person field to accept multiple values, how do we pass more than one value in the REST call? Since we normally separate user names with semicolon in peop...
12 comments
Read more

Document ID Service - New Feature in SharePoint

When I explored this new feature of SharePoint in Online (this feature is available in SharePoint 2013 also), I found it interesting because it solved one of the common problems people faced in SharePoint. I will first explain about this feature and I'll cover the problem and the solution in another post. This feature allows Site Collection Admin assign a unique ID to every document in the site collection out of the box. If you recall, there was no out of the box way to assign a unique number to the documents across libraries in a site collection. Yes ID column is unique but only within a library. Document ID column contains a number which is unique across libraries. To start with, let's have a look at a typical document library. This is a familiar view for SP guys. By default, you don't get Document ID column in a library. All Items View To get Document ID column, you need to activate a site collection feature "Document ID Service" as shown below. S...
Post a Comment
Read more