5 Replies Latest reply on Jul 18, 2018 2:26 AM by Ivan Casana-Gallen

# Detecting overlapping shifts

Hello,

Let´s imagine that we have a team of domiciliary teachers who need to help some students to do some preparation for their final exams.

Let´s imagine that we have a spreadsheet with the hours that each of these students has requested.

Is there a way of detecting if there are any overlaps or double ups?

In the data given below  you will be able to see there is an overlap between the third and the fourth lesson.

Start time
Finish Time
Name
01/06/2018   09:0001/06/2018   12:00Student 1
05/06/2018 19:0005/06/2018  21:00Student 1
10/06/2018 10:0010/06/2018 12:00Student 2
10/06/2018 11:0010/06/2018 13:00Student 2

Thank you

• ###### 1. Re: Detecting overlapped shifts

Hello Ivan,

Here's a sample, which simply checks if the row overlaps the previous row, using Table Calculation.

Regards

Lei

• ###### 2. Re: Detecting overlapped shifts

Hi Lei,

I would like to ask you something. In order to identify overlapping shifts we need to make sure that the name of the student is the same one. Does your calculation capture this bit of information?

Thank you

Ivan

• ###### 3. Re: Detecting overlapped shifts

Hello Ivan,

Ivan Casana-Gallen wrote:

In order to identify overlapping shifts we need to make sure that the name of the student is the same one. Does your calculation capture this bit of information?

Good question!

Yes, student name has been taken into account.

Table calculation is restarting every [Name].

Regards

Lei

• ###### 4. Re: Detecting overlapped shifts

Hi Lei,

Thank you so much for your help. I am trying now to identify the shifts these double shifts are paired to.

IF [Check double shifts]='Doubled' THEN ([Check double shifts]),-1) = 'Pair'

ELSE 'OK'

END

I have tried several options but can't get them to work.

Thank you

Ivan

• ###### 5. Re: Detecting overlapped shifts

Hi Lei

This is working

IF FIRST()=0 THEN 'OK'

ELSEIF LOOKUP(ATTR([Planned End Date And Time]),0)>LOOKUP(ATTR([Planned Start Date And Time]),1) THEN 'Pair'

ELSE'OK'

END

However, I guess there must be an easier way of getting tableau to basically show you the cell above.

Thank you

Ivan