none
Validation Formula to prevent future date entry

    Question

  • 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

Answers

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

    =todday-data

    3 Configure Validation setting at list level and use this formula

    =AND(calc>0;WEEKDAY(data)=1)

    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:

    http://akanoongo.blogspot.jp/2010/02/common-date-time-formulas-for.html

    Thanks,

    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 tnmff@microsoft.com.

    • Marked as answer by rixmcx59 Tuesday, February 2, 2016 8:16 PM
    Tuesday, February 2, 2016 8:43 AM
    Moderator
  • 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.

    Thanks

    Tuesday, February 2, 2016 8:20 PM