SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm trying to write a column validation for a SharePoint calendar to ensure all bookings are after 8.30am in the morning and before 5pm at night.

I've tried the following:

=AND(TIME(8,3,0) < [StartTime], TIME(17,0,0) > [StartTime])

But it doesn't work.

Can anyone advise how to successfully compare a column against a specific time?

Thank you!

share|improve this question
up vote 1 down vote accepted

I am assuming you are using the Start Time field from a standard Calendar list which is actually a Date/Time value so you need to make sure you are comparing like with like. Also, the second number in your TIME function is minutes so should be 30 in the first instance.

=AND(TIME(8,30,0) < TIME(HOUR([StartTime]),MINUTE([StartTime]),SECOND([StartTime])), TIME(17,0,0) > TIME(HOUR([StartTime]),MINUTE([StartTime]),SECOND([StartTime])))

I have not actually tested this formula so it might need a little tweaking but hopefully points you in the right direction.

share|improve this answer
That worked - thanks for pointing me in the right direction :) – Averenix May 13 '13 at 22:41
Excellent - you are welcome. – Dave Paylor May 14 '13 at 8:18
Thanks, my scenario was different but it helps me a lot. :) – Utkarsh Yeolekar Dec 17 '14 at 11:17

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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