• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Overlapping Dates, Gaps in Dates, Double Counting

S

sglxl

New Member
Hi all, This is my first post. Would appreciate your assistance,


I am using Windows 7 and Excel 2010


I have the following problem with users compiling a database for a hire car company.


No Hire Car Start date End Date Days on Hire Days off Hire

1 ABC1234 1 Jan 12 12 Jan 12 12

2 ABC1234 25 Jan 12 2 Feb 12 9

3 ABC1234 10 Jan 12 15 Jan 12 6

4 XYZ7890 15 Feb 12 23 Feb 12 9

5 XYZ7890 4 Mar 12 15 Mar 12 12

6 XYZ7890 18 Feb 12 22 Feb 12 5


Now what I need to do is warn the user someway (new worksheet report??, using conditional formatting?? Gantt Chart??) that there are missing days where the car is off hire - between entries 1 and 2 from 13 Jan 12 to 24 Jan 12 - which need to be recorded and that there has been entered an overlapping period between 10 Jan 12 and 12 Jan 12 (Entries 1 and 3) - in other words we are double counting for 2 days.


Also with Car XYZ7890 we are double counting between 18 and 22 Feb 12 and we are off hire from 24 Feb 12 to 3 Mar 12


I would prefer that I do this using VBA using say a Worksheet Event as the workbook is too large to use functions in each cell to evaluate overlaps etc


Thanks/sglxl
 
Ok, for this I think I would use a formula in the Days off Hire column ,,, to kick this off in the Days on Hire column I would have a calculation to work out the number of days between two dates ,, DATEDIF should be ok ( or NETWORKDAYS ), in the Days off Hire column I would put a second calculation that would look at the end date of car ABC1234 and the next row down for that car and put =IF(ISERROR(DATEDIF(D3-1,C4,"d")),"CHECK",DATEDIF(D3-1,C4,"d"))

This would return the CHECK if there was an overlap of dates.
 
@all

Hi!

About function DATEDIF it'd be interesting to read this topic, even if with "d" parameter it's supposed to be harmless:

http://chandoo.org/forums/topic/excel-2007

Regards!
 
Hi ,


Can you check this workbook here ?


https://docs.google.com/open?id=0B0KMpuzr3MTVajlpdkNBcno3X1k


Download the file , and open it in Excel. Your original data is in Sheet4 , and the macro is to be run on Sheet1.


Narayan
 
Hello Everyone,


I'm having the similar case but with around 15000 rows of data. I have say 3 columns, Personal ID, Start date and Stop date. There are several events for a particular Personal ID.


Data looks like:


id startdate enddate

1 13-Mar-2012 9-Apr-2012

1 12-Apr-2012 7-May-2012

1 8-May-2012 4-Jun-2012

1 5-Jun-2012 29-Jun-2012

1 30-Jun-2012 30-Jul-2012

2 16-Sep-2012 29-Sep-2012

2 30-Sep-2012 13-Oct-2012

2 14-Oct-2012 10-Nov-2012

2 11-Nov-2012 8-Dec-2012

2 9-Dec-2012 5-Jan-2013

3 24-Jul-2012 20-Aug-2012

3 21-Aug-2012 17-Sep-2012

3 18-Sep-2012 15-Oct-2012

3 16-Oct-2012 12-Nov-2012

3 13-Nov-2012 21-Nov-2012


I need to find out whether there are any

1. Overlapping date ranges? or any

2. Gaps (missing dates) between an Initial start date and Last stop date? for a particular Personal ID.


I was searching for a solution since so long and found out that the Macro Enabled Excel File by NARAYAN gives desired out put and as expected (only limited to the data present in that file).


@ NARAYAN, I copy pasted data (sorted by ID, then by start date, then by end date) from my worksheet into the file provided by you, I defined the "Data_Range" into Excel as per my data (~ 15000 rows) and ran the macro but the output has not all the Overlapping dates and Gaps. Output is improper when considering large data..


Please help me out at this stage..if I'm missing out anything or if I need to perform any task at some point like formatting, specifying any range or do I need to adapt the code?


Please note: There are around 15000 rows.


Thanks

-Arun
 
Hi Arun ,


Give me some time ; I'll go through the earlier data setup and code , and see what changes need to be made.


Narayan
 
Hi Arun ,


I have seen that the code does not take the ID into account in deciding the missing dates ; also it does not detect overlapping dates between IDs ; in case you wish these two cases to be taken care of , please let me know.


The code logic will have to be changed a lot to take care of the above requirements ; in case you need this urgently , I will not be able to do it ; I hope someone else can take this up.


Narayan
 
Hello Narayan,


Thanks for re-looking the code. You can ignore the second case where Overlapping dates between IDs is concerned; not required.


But the first case is mandatory; i.e. code must take ID into account in deciding the missing dates.


To make it simple, Both Overlapping dates and Missing dates should be Per ID, Not between IDs.


Please take your time.. Not that urgent.. I'm also looking for the resolution simultaneously.


-Arun
 
You must log in or register to reply here.
Back
Top