Calendar Column Validation for booking after a specific time

Question

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!

score 1 · Accepted Answer · answered May 13 '13 at 09:00

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.

answered May 13 '13 at 09:00

Dave Paylor

4,019
2
14
8

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 08:18
Thanks, my scenario was different but it helps me a lot. :) – Utkarsh Yeolekar Dec 17 '14 at 11:17

Calendar Column Validation for booking after a specific time

1 Answers1