My Alaskan Friends asked to know how I check for overlaps. This method depends on ISP Data export to Excel and then nested Excel IF formulas.  I’m a big fan of Excel Macros to eliminate repetitious key strokes and to prevent incorrect typing, do it right once and then let the Macro repeat it.  This explanation assumes you understand how Excel IF functions work. Get in touch if you want more details.

Step 1. Search ISP Data for all possible programs that could overlap. Ctrl and click will let you select all the programs you want to check for overlaps. I do not check all the programs. For example, as I understand it a Care Coordination meeting could overlap with Day Hab but would not be a problem, therefore I do not select Care Coordination. For me I select Day Hab, Hourly Respite, Supported Employ, In Home Supp, Supported Living, and Daily Respite. I may have missed one there but you get the idea. Export the search to Excel.

Step 2. Create columns for the Excel function TIMEVALUE for both the Begin Time and End Time. You need the timevalue function because the times are exported as text and without timevalue 1:00 pm sorts as an earlier time than 8:00 am.

Step 3. Sort the data by name, date, timevalue(begin time), and timevalue(end time). I’m not sure you need the timevalue(end time) but I do it anyway.

Step 4. Create column for overlap. In this column you insert a nested IF function. You are going to compare the current row with the data in
the row above it. The nested IF function is:

If the Individual Name is the same as the row above

  • TRUE, next IF Function
  • FALSE, Not an overlap

If the Date is the same as the row above

  • TRUE, next IF Function
  • FALSE, Not an overlap

If the current row timevalue(Begin Time) < timevalue(End Time) of the row above

  • FALSE, Not an overlap

The actual function looks something like this:  =IF(A2=A3,IF(B2=B3,IF(C3<D2,”OVERLAP”,””),””),””) Where column A is the Individual’s name, column B is the Date, column C is the timevalue(Begin Time), and column D is the timevalue(End Time). If there is an Overlap the cell will read OVERLAP, if there is no overlap the cell will be blank.

Hope this helps.