I need to validate entered data to make sure time ranges are not overlapping with ranges already entered.
Users will use a form to enter events that take place on the same date. The main fields on the form are tDate, tStartTime, and tEndTime which for example will show something like 8:00:00 AM for start time and 12:00:00 PM for end time. There is also a field for empID to store entered times for each employee on a particular date.
I have a submit button set as boolean and use before update to check the status of that button so no data is entered into the table until the submit button is clicked. When clicked I have some standard validation to make sure fields on the form are not null and check to make sure the tEndTime > tStartTime and if so msg user they must select time an end time after start time. What I need to come up with is validation rules that will check to see if a newly entered time range overlaps any of those already entered by that empID and on that tDate and msg the user to correct this issue before the update will take place.
There may be multiple entries by an employee on any given date.
Typical data might be as follows.
tDate tStartTime tEndTime
5/1/2017 8:00 12:00
5/1/2017 12:30 2:00
5/1/2017 2:00 5:00
When entering a new record users should not able to select 8:30 to 11:00, they should not be able to select 12:00 to 1:00 - I dont want users to create any overlapping conflict.
I am struggling with how to create validation rule so any guidance will be greatly appreciated - thanks!