Interesting Date/Time Calculation Query - Please Help!
fle...@nospamhotmail.com
I have an interesting problem regarding Date/Time calculations and
comparisons.
I am tasked with coding a routine to find if a date/time range falls
within another date/time range. For example: If a working day is
between 09:00 and 18:00, and there is an appointment from 13:00 to
14:00, I need to know if that "appointment" date falls within the
"working day" range.
Now then...I have managed to achieve this with a routine that's
included below, but I'm not convinced it's the best way to accomplish
such a task.... The thing is that this is only half of the problem.
Ultimately, I'll have a "main" date/time range (ie. 09:00 to 18:00)
and I'll have many different appointments within (or possibly outside)
of that range. What I ultimately need to achieve is to show all the
ranges of time that are available (ie. there's no appointment made)
For example:
The "main" time window is 09:00 to 18:00..
There's an appointment at 10:00 to 10:30, another one from 11:30 to
13:00, and yet another from 14:00 to 15:30.
I need to process this data to return the "free time" result..
With the example above, the "free time" would be:
09:00 to 10:00 and
10:30 to 11:30 and
13:00 to 14:00 and
15:30 to 18:00.
Now, I have previously achieved this, but it was using logic similar
to that shown in my routine below, and it takes an AGE to calculate.
In these examples above, I've restricted the ranges to a day's worth
of hours. In practice, these queries could span a week, a month, or
even several years!
My question would be, can anyone offer any insight into a better (ie.
faster) method of achieving such results. Any and all help on this
problem is greatly appreciated.
Here's my "DateInDate" routine below. It works, but it's a very clumsy
way to determine if one date range falls within another one.
(For those not interested in examining the routine, I am essentially
splitting each date range into the minutes within that range, and by a
process of looping round within a loop (very time consuming!)
examining whether each specific minute matches (thus falling within
the range) or not)
----- Cut Here -----
Option Explicit
' #######################################################
' ## Function DateInDate
' ##
' ## Date : 20 July 2000
' ##
' ## This function will find a set of TWO dates within
' ## another set of TWO dates. The function returns TRUE
' ## if the "Schedule" date period falls within the "Main"
' ## date period, and will return "FALSE" if the "Schedule" date
' ## period is totally outside of the range of the "Main"
' ## date period.
' ##
' ## The function takes FIVE parameters, described as
' ## follows:
' ##
' ## pMainDateStart (Type: DATE)
' ## pMainDateEnd (Type: DATE)
' ## pScheduleDateStart (Type: DATE)
' ## pScheduleDateEnd (Type: DATE)
' ## pAllowStartEndOverlap (Type: BOOLEAN)
' ##
' ## pMainDateStart and pMainDateEnd are the start and end
' ## dates/times of the main "window" of time that you wish
' ## to examine.
' ## pScheduleDateStart and pScheduleDateEnd is the expanse
' ## of time that you wish to determine wether or not it
' ## falls inside or outside of the range of the "Main" time
' ## "window". The pAllowStartEndOverlap boolean variable
' ## is used if you wish to allow the Schedule Start/End
' ## date/time to coincide with the "Main" Start/End
' ## date/time. This explanation is probably as clear as
' ## mud, so here's some examples:
' ##
' ## Example 1
' ## Main Start: 09:00
' ## Main End : 17:00
' ## Schedule Start: 14:00
' ## Schedule End: 15:00
' ## Result: The function will return TRUE. This
' ## is irrespective of the
' ## bAllowStartEndOverlap value.
' ##
' ## Example 2
' ## Main Start: 09:00
' ## Main End : 14:00
' ## Schedule Start: 14:00
' ## Schedule End: 15:00
' ## Result: The function will return TRUE is the
' ## bAllowStartEndOverlap value is FALSE,
' ## but will return FALSE if the
' ## bAllowStartEndOverlap value is TRUE.
' ##
Public Function DateInDate(pMainDateStart As Date, pMainDateEnd As
Date, _
pScheduleDateStart As Date, pScheduleDateEnd As Date, _
pAllowStartEndOverlap As Boolean) As Boolean
Dim MainDate() As Date
Dim ScheduleDate() As Date
Dim lMainMinutes As Long
Dim lScheduleMinutes As Long
Dim lCounter As Long
Dim lCounter2 As Long
DateInDate = False
lMainMinutes = DateDiff("n", pMainDateStart, pMainDateEnd) + 1
lScheduleMinutes = DateDiff("n", pScheduleDateStart, pScheduleDateEnd)
+ 1
ReDim MainDate(lMainMinutes)
ReDim ScheduleDate(lScheduleMinutes)
For lCounter = 1 To lMainMinutes
MainDate(lCounter) = DateAdd("n", (lCounter - 1), pMainDateStart)
Next lCounter
For lCounter = 1 To lScheduleMinutes
ScheduleDate(lCounter) = DateAdd("n", (lCounter - 1),
pScheduleDateStart)
Next lCounter
For lCounter = 1 To lMainMinutes
For lCounter2 = 1 To lScheduleMinutes
If DateDiff("s", ScheduleDate(lCounter2), MainDate(lCounter))
= 0 Then
If pAllowStartEndOverlap = True Then
If (lCounter = 1) Or (lCounter = lMainMinutes) Then
' Do Nothing!
Else
If DateInDate = False Then
DateInDate = True
End If
End If
Else
If DateInDate = False Then
DateInDate = True
End If
End If
End If
Next lCounter2
Next lCounter
End Function
----- Cut Here -----
Once again, any and all help on this problem is greatly appreciated.
I'm almost certain there's a MUCH better and quicker way to achieve
this, but my brain is frazzled enough by this sticky little problem!
Thanks again...
Best Regards,
Craig.
Rick Rothstein
*think* it does what you are asking (your example looks like it misstated
how to use the bAllowStartEndOverlap argument):
Function DateInDate(pMainDateStart As Date, _
pMainDateEnd As Date, _
pScheduleDateStart As Date, _
pScheduleDateEnd As Date, _
pAllowStartEndOverlap As Boolean) _
As Boolean
If pScheduleDateStart > pMainDateStart And _
pScheduleDateEnd < pMainDateEnd Then
DateInDate = True
ElseIf pAllowStartEndOverlap And _
((pScheduleDateStart >= pMainDateStart And _
pScheduleDateStart <= pMainDateEnd) Or _
(pScheduleDateEnd >= pMainDateStart And _
pScheduleDateEnd <= pMainDateEnd)) Then
DateInDate = True
End If
End Function
Rick
<fle...@NOSPAMhotmail.com> wrote in message
news:qjkens8ntkkg30keg...@4ax.com...
fle...@nospamhotmail.com
>> [SNIP!]
Rick,
Thanks for your routine. This was how I first started to implement my
routine, however I got caught out with VB's inherent problem when
using arithmetic operators to compare dates/times.. If you're unaware
of this, please see article "Q194894 PRB: Date Comparison Can fail
Using Arithmetic Operators" in the Microsoft Knowledge Base.
I'm currently looking at some code that was posted by Larry Serflaten
in microsoft.public.vb.general.discussion which shows an entirely
different methodology for tackling such a task. It's much quicker,
too! I appreciate your efforts in regard to this problem, so thanks
again.
Best Regards,
Craig.
J French
Time would overlap with another :
This is how I would do it :
1) Build a string of :
Chr$(Year) + Chr$(Month) + Chr$(Day)
+ Chr$(StartHour) + Chr$(StartMin) + "B"
Another string of :
Chr$(Year) + Chr$(Month) + Chr$(Day)
+ Chr$(EndHour) + Chr$(EndMin) + "E"
2) Build a sorted array of appointments
(Start and End records for each appointment)
3) Binary chop for the position of the 'new appointment'
ie: find its insertion position
If the appointment before is a "B" type then it is booked
If the appointment before is an "E" type then check the
new appointment's "E" type to make sure it is less than
the next record - or the next record is UBound( Array )
I would be inclined to keep this 'Array' on disk
You could 'block out' non working hours by having 'Time Off' records
ie: "Ba" "Bo" "Ea" "Eo" {a}tive {o}ff
I would be very wary of using any locale specific routines.
The machines locale is the responsibility of the user - but who says
that users are responsible.
I also realize that using two records could be reduced to using one
record - but you'd not save much - and it increases the complexity of
the system.
You could also use Julian/Gregorians - but the same thing applies.
I would also 'wrap' the encoding/decoding of dates into a class.
I hope I haven't missed the point completely .....
Good Luck - Jerry