Working with dates for multiple entries

[Potoroo]

Using Excel 2003 on XP.

I have a spreadsheet which contains over 5000 rows of data about employees and their leave.

Column A contains the employee's identification number. Column I contains the start date of the leave and Column J contains the end date of the leave. Column L shows the number of days applicable to the leave period. There may be more than one row for the same employee but the leave dates will be different.

What I need to do is to find any entries for each employee where one period of leave immediately follows a previous leave period (sometimes these are entered separately into the system). E.g. An employee may have one period of leave which shows the end date as 6 June, 2007 and the next leave entry may show the start date as 7 June, 2007. I also need to determine the number of days between each period where they start date of one leave period does not immediately follow the end date of a previous period.

Due to the size of the spreadsheet, using filters is simply not an effective way to view these.

Could someone please give me a nudge in the right direction about the best way(s) to approach this?

TIA.

[nattasiray]

Please post some sample file and the result that you want.

[Potoroo]

Hello,

Thank you for the offer of help.

I have attached a sample file (the names have been changed to protect the innocent!).

I have added comments into the file and have highlighted a couple of examples. Bascially, I need to locate where one individual has more than one leave entry where one period of leave ends and another immediately starts (dates follow on). I also need to know if there is a gap between the end date of one leave period and the start date of another leave period as well as the number of calendar days between these dates (gap days). I don't really care how the result shows on the spreadsheet. Helper columns would probably provide the best searchable area so having results say something like "Follows on" or "Gap exists" with the number of days in another column would be great. Happy with TRUE/FALSE or 0/1 if those are the best results which can be returned.

Many thanks for your assistance.

[Potoroo]

I think I have solved my own problem but I would still appreciate seeing a better solution if anyone can help.

The way I have solved this currently is by sorting the data by the employees' identification number then by Start Date. In a helper column I used

Please Login or Register  to view this content.

Where the employees' identification number changes to the next individual, I get a #NUM! error which I did a "Find" and "Replace" with a blank to clean it up.

I then used a macro to insert a blank row each time the employees' identification number changed to clearly show what each individual was up to. For this insertion I used:

Please Login or Register  to view this content.

It's slow and clunky but I managed to get a result.

I would still appreciate any input for a better solution.

Many thanks.

[Potoroo]

Hmmm ... It isn't the solution I first thought.

If an individual changes in Col A and the dates flow in a forward direction, I get a false result. I see the number of days between the end date of one person's leave and the start date of the next person's leave. I need to be able to get around this.

Suggestions please?