#1
|
|||
|
|||
How to find overlapping dates
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 |
#2
|
||||
|
||||
Quote:
2. When you say 'track', how do you want to see/report this tracking? What version of Excel are you using? |
#3
|
||||
|
||||
I can do something like this
2023-07-30_152458.jpg |
#4
|
|||
|
|||
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. |
#5
|
||||
|
||||
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. |
#6
|
||||
|
||||
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. |
#7
|
|||
|
|||
Thanks, this looks great. I'll see If I can make it work on my spreadsheet
|
#8
|
|||
|
|||
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. |
#9
|
|||
|
|||
Hi ArviLaanemets,
Thank you for your help. This is exactly(!!!) what I was looking for |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Range method Find can't find dates | jmcsa3 | Excel Programming | 1 | 05-02-2020 06:56 AM |
How do you use the find and replace tool to find dates and times in Excel 2013? | Jules90 | Excel | 3 | 04-14-2020 07:40 PM |
Pictures overlapping eachother on the same slide | kaiks | PowerPoint | 1 | 11-03-2015 08:50 PM |
Find and Replace: Dates | Attirb | Word | 2 | 04-13-2011 09:56 AM |
Lots of overlapping appointments! | MushyPeas | Outlook | 2 | 01-21-2009 10:01 AM |