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.


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


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. – Quinn Johns Mar 26 '15 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 '15 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 '15 at 2:27
  • Try the updated answer. – Amal Hashim Mar 26 '15 at 2:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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