I have received a few questions regarding scenarios where a Super User has been tasked with creating a simple time sheet form such as:
Employee Name: People & GroupsStart Time: Date & Time
End Time: Date & Time
Description: Multiple Lines of Plain Text
This form in SharePoint would take about 30 seconds to create, but how do you enforce the business rules which makes it usable?
· Start Time date must equal the End Time date
· End Time (hours & minutes) must be after the Start Time (hour & minutes)
Well the good news is I have created the formula to do exactly the above!
In order to create this validation:
1. Navigate to your list
2. Select List Tools
3. Select List
4. Select List Settings
5. Select Validation Settings
6. Use this formula
a. =AND(DATE(YEAR([End Time]),MONTH([End Time]),DAY([End Time]))=DATE(YEAR([Start Time]),MONTH([Start Time]),DAY([Start Time])),TIME(HOUR([Start Time]),MINUTE([Start Time]),SECOND([Start Time]))<TIME(HOUR([End Time]),MINUTE([End Time]),SECOND([End Time])))
7. Set your User Message (message the user receives when they break the rules!)
This formula will force the above business requirements and just swap out Start Time & End Time with whatever your Start & End field are called.