VBA - A Problem with Dates.

[NateBuckley]

Greetings intrepid VBA users!

I have a problem, a problem with dates.

I have a Constant criteria of 20

I also have a data range of different records that contain two dates,

DateFrom - the date the holiday starts
DateTo - The date the holiday ends

I'm trying to check when a user requests a new hoilday it will check all
these records, and see if this new holiday (which contains two dates, dateTo,
dateFrom) can be processed. It CAN'T be processed if 20 hoildays from records
are in affect when the user wants a holiday.

E.G

I have a bucket load of holidays, 20 of these already exist from 04/04/08 to
06/04/08.

Can't except anymore for them dates, BUT I can't also accept any dates that
partially cover these dates so if the user inputted DateTo = 05/04/08 to
08/04/08 that would also tell the user that he'd have to change dates.

Thank you in advance for any help given!

I hope I have explained myself clearly, if not, then I'll attemp to explain
again.
I think I may just need a clever IF statement in vba code, not sure though.

 

[Ivyleaf]

Greetings intrepid VBA users!

I have a problem, a problem with dates.

I have a Constant criteria of 20

I also have a data range of different records that contain two dates,

DateFrom - the date the holiday starts
DateTo - The date the holiday ends

I'm trying to check when a user requests a new hoilday it will check all
these records, and see if this new holiday (which contains two dates, dateTo,
dateFrom) can be processed. It CAN'T be processed if 20 hoildays from records
are in affect when the user wants a holiday.

E.G

I have a bucket load of holidays, 20 of these already exist from 04/04/08 to
06/04/08.

Can't except anymore for them dates, BUT I can't also accept any dates that
partially cover these dates so if the user inputted DateTo = 05/04/08 to
08/04/08 that would also tell the user that he'd have to change dates.

Thank you in advance for any help given!

I hope I have explained myself clearly, if not, then I'll attemp to explain
again.
I think I may just need a clever IF statement in vba code, not sure though..

Hi Nate,

As far as I can see you'd have to loop through all the existing
records and for each one do the following:

Call Start1 and End1 the dates of the existing record.
Call Start2 and End2 the date of the proposed leave.
We also need a counter starting at 0 : i =0

Do Until i = 20
Set Start1 and End1 to new row / record whatever.
If Not(End1 < Start2 Or Start1 > End2) Then i = i + 1
Loop

If i = 20 Then
MsgBox "No Holidays for you!"
Else
MsgBox "Good choice, enjoy your break!"
End If

I hope I got the order of the dates correct in the overlap
exclusion... it's very late over here

Cheers,
Ivan.

 

[NateBuckley]

Cheers Ivy, sorry about the amount of time it took to reply, but that will do
the trick.

Thanks again!