Overlapping of Dates

iceshock

Forums

Hello, my current data include the travel's start date and end date. 
 
I hope to identify duplicate entries(employee ID) with all overlapping travel date. May i know how can i go about doing it?
 
 
regards,iceshock

Steven Luciani Thu, 03/08/2018 - 15:44

Hi Iceshock,

You can do this with the equation below. Prior to applying the equation you must index your file by employee ID and start date. Once this is done, create a virtual numberic field using this equation:

@if(EMPNO=@GetNextValue("EMPNO"),@age(@GetNextValue("start_date"),END_DATE),0)

Then you can isolate on any negative numbers. Any employee with negative numbers have overlapping travel dates.

See quick sample below.

Cheers,

Steve