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!


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.

| 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 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.