Hi Everyone,
Is there a function that will allow me to track overlapping leave requests?
I have attached sample spreadsheet.
My Excel doesn't have FILTER function.
Thank you
Agnieszka

Attached Files

Overlapping dates request.xlsx (10.2 KB, 4 views)

1. Overlapping ignoring all names and leave types, that is any overlapping dates in the whole table? Or maybe just by Name? Or both Name and leave type?
2. When you say 'track', how do you want to see/report this tracking?
What version of Excel are you using?

I can do something like this
2023-07-30_152458.jpg

Hi p45cal,
I have Excel 2016.
I need to find out who applied for overlapping leave so will need to search by names.
What I was hoping for was the function that I could write in column E that would return either "overlap" or "do not overlap" (or "false", "true"...). The return would be for the same person mentioned again somewhere down the lines. Then I could filter the table based on column E and A (Name).
I'm not sure what you did in the table you posted but, I may have few hundreds lines on each report and won't be able to manually search through them.

With Excel 2016 you have Power Query (aka Get & Transform Data) built in. All you'll need to do is to update the table on the left with your few hundred rows of data, then right-click somewhere in the table on the right and choose Refresh.

As to a formula for column E, quite difficult, but maybe possible with a slightly different Power Query query. I'll have a think on it.

Attached Files

msofficeforums51161Overlapping dates request.xlsx (20.9 KB, 5 views)

See attached. Same as before regarding refreshing to update the second table. Rudimentary formula in column E.

Note, in your file, in row 7 you had TIm with a capital I; my queries are case sensitive so I changed the I to an i. Instead, I can make the query case insensitive if you wish.

Attached Files

msofficeforums51161Overlapping dates request_v2.xlsx (19.8 KB, 6 views)

Thanks, this looks great. I'll see If I can make it work on my spreadsheet

Here is as I would do this (Except I'd use Defined Tables, and I'd add a table where all employees are listed, to allow the formulas to expand automatically whenever a new employee is added, or leaves for next year are added)

The sheet LeaveCal may contain dates for any reasonable number of future years, sou you don't have to edit it at every new year, and unless you want to see it for some reason, you can then hide this sheet, so users don't mess with it.

Attached Files

Overlapping dates request.xlsx (36.4 KB, 12 views)

Hi ArviLaanemets,
Thank you for your help. This is exactly(!!!) what I was looking for