Detecting Duplicate and Overlapping Leave Entries.

Anoop Phago

Hi everyone!

I'm working on a Smartsheet formula to identify duplicate leave entries. My goal is to flag cases where the Name, Leave Type, Start Date, and End Date either match exactly or overlap. For example, if someone submits leave from January 24 to January 27, and then submits another leave from January 25 to January 26, I want both entries to be marked as duplicates because the dates overlap.

I've tried using the COUNTIFS function to compare the name, leave type, and date ranges, but I keep running into syntax issues. The main challenge is correctly handling date comparisons within the formula. If anyone has suggestions or a working formula to accurately detect duplicates and overlapping dates, I would really appreciate it!

Thanks in advance for your help!

prime_nathaniel

@Anoop Phago

I am assuming you already have a handle on name, leave type, and exact matches for start and end. If you do not just follow the instructions on the formula and JUST test these filters first.

To do an overlap you have to do multiple comparisons OR collections. Note that either approach uses a massive number of comparisons as you are doing 1 cell against pretty much every other cell multiple times.

Date 1 =

January 24 to January 27

Date 2 =

January 25 to January 26

To determine an overalp your algorithm should be,

1. check that any start date falls between your current row's start and end as any start date between your current record's dates is an overlap.
2. check that any end date falls between your current row's start and end as any end date between your current record's date is an overlap.
3. If either of the above is true then an overlap must exist

count of any start dates falls between range, =countifs([start]:[start], >= start@row, [start]:[start], < = end@row) > 0
count of any end dates falls between range, =countifs([end]:[end], >= start@row, [end]:[end], < = end@row) > 0

combine the two with OR

has overlap = or(countifs([start]:[start], >= start@row, [start]:[start], < = end@row) > 0, countifs([end]:[end], >= start@row, [end]:[end], < = end@row) > 0)

very important you will need to add your other filter criteria in for countif so make sure you do name = name, leave type = leave type, etc (which on that note, I would not recommend using leave type as a filter). In real world application it is pretty common to see overlaps for say bereavement leave and normal PTO / FTO or other such cases.

It is important to note you can mark things as duplicate using this but it wont associate them together other than just because they are duplicates. This should be logically obvious as you could have #n overlapping dates using this system on its own. As long as your approvers keep up on duplicates and sort, it really shouldn't be an issue as you'd only ever be dealing with one or two entries ever not theoretical thousands.

If you DID need thousands of related overlapping records you would need to use the calendar days of the year as your unique to tie them to SOMETHING. Then you'd see overlaps more like a gant overlap against time, as far as I know this is pretty much overkill for anything being done outside of a dedicated custom application.

Anoop Phago

Thanks @prime_nathaniel

I used the formula below, but it's not working.
=OR(COUNTIFS([Start Date]:[Start Date], >= [Start Date]@row, [Start Date]:[Start Date], < = [End Date]@row > 0), countifs([End Date]:[End Date], >= [Start Date]@row, [End Date:[End Date], < = [End Date]@row > 0))

Paul Newcome

You don't need the OR function to compare multiple COUNTIFS. You can do this inside of a single COUNTIFS like so:

=IF(COUNTIFS(Name:Name, @cell = Name@row, [Leav Type]:[Leave Type], @cell = [Leave Type]@row, [Start Date]:[Start Date], @cell <= [End Date]@row, [End Date]:[End Date], @cell >= [Start Date]@row) > 1, 1)