Validation Formula to prevent future date entry


  • Hello all, I have this validation part working '=WEEKDAY([Week of])=1' which forces Sunday as the start of the work week using this cacl column "=Today-WEEKDAY(Today)+1'.  Is there any way to force only past Sundays? like  AND [Week of]<TODAY().  We want staff to choose a Sunday but not fill out time in the future.

    Thanks for any help

    Tuesday, February 2, 2016 12:01 AM


All replies

  • Hi

    i reproduced your request on O365 and it's working

    1. create a new date type column , name it as todday ( if necesarry make it hidden ) with default value Today

    2. create a calculated column , name it calc and use the formula


    3 Configure Validation setting at list level and use this formula


    Ina  on-premises env , change ";" with "," in the last formula

    Romeo Donca, Orange Romania (MCSE, MCITP, CCNA) Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.

    Tuesday, February 2, 2016 7:41 AM
  • Hi rixmcx,

    You can type like this: "=AND(WEEKDAY([Week of])=1,[Week of]<TODAY())". Using TODAY function is easier.

    More information about WEEKDAY() for your reference:


    Dean Wang

    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact

    • Marked as answer by rixmcx59 Tuesday, February 2, 2016 8:16 PM
    Tuesday, February 2, 2016 8:43 AM
  • Works like a charm,  just to clarify for other users, put this in the list validation "=AND(WEEKDAY([Week of])=1,[Week of]<TODAY())" and this "=Today-WEEKDAY(Today)+1" in the default value of your date column.


    Tuesday, February 2, 2016 8:20 PM