I'm working with a date and time column and need a validation that returns true if the day of the week is Monday OR Friday. Users are not permitted to add an item on any other day of the week.

=TEXT([Date & Time],"dddd")="Monday"

This formula works perfectly for allowing only dates that are a Monday, I just need to add Friday into the same validator.

2 Answers 2


Why, why all those nested IFs??

The WEEKDAY function returns a number 1-7 for weekdays, starting at 1=Sunday

So to validate Monday, Thursday and Friday use:

CHOOSE( WEEKDAY([Start Date]) ,false ,true ,false ,false ,true ,true ,false )

An optional second parameter makes the WEEKDAY function start at other days of the week

  • Danny thank you so much, I can adapt this easily for future requests too. Cheers
    – Steven
    Apr 2, 2015 at 0:16
  • Have since documented all Functions and Syntax you can use in Calculated Columns. Jul 12, 2015 at 15:11

Try this

=IF(TEXT([Date & Time],"dddd")="Monday",TRUE,IF(TEXT([Date & Time],"dddd")="Friday",TRUE,TEXT([Date & Time],"dddd")="Thursday"))
  • @Steven if this solution worked, please mark as the answer and/or rate up the answer. Mar 26, 2015 at 0:48
  • I have, it wouldn't let me mark it as the answer straight away. Said I had to wait 5 mins..
    – Steven
    Mar 26, 2015 at 0:50
  • Amal I've just had the request changed to include Monday, Thursday, and Friday. Are you able to help me with altering the formula to include Thursday as well?
    – Steven
    Mar 26, 2015 at 2:27
  • Try the updated answer. Mar 26, 2015 at 2:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.