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!

1 Answer 1


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.

  • That worked - thanks for pointing me in the right direction :)
    – Averenix
    May 13, 2013 at 22:41
  • Excellent - you are welcome. May 14, 2013 at 8:18
  • Thanks, my scenario was different but it helps me a lot. :) Dec 17, 2014 at 11:17

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.