List Validation Formula: Force Business Rules For Time Sheets

Hello!

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 & Groups

Start 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?

Business Requirements:

·         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!)

8.    Complete

This formula will force the above business requirements and just swap out Start Time & End Time with whatever your Start & End field are called.

Best Regards,

Dan