Solved

Calculate Time Due From a DateTime value in business intervals

Posted on 2003-11-07
11
679 Views
Last Modified: 2008-02-01
I am developing an app that logs the Date & Time when an item is received. The item then has to be delivered to the recipient within a specified time, but taking into account normal business hours. ie if an item is received at 0800hrs with a target of 8hr delivery time, the due time is 1700hrs. Conversly, if another item is received at 1500hrs, the due time is 1300hrs next day. This is based on an 8 hour working day (8am - 5pm) and an 8 hour target time, also taking into account 1 hour lunch break.
So what I need to do is write a function that will calculate the due time when given the received time and target time.

Has anyone got a formulae to do this or similar?

CD
0
Comment
Question by:CD-Softy
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 4

Expert Comment

by:brother7
ID: 9702544
1- Lunch is from 12PM - 1PM?
2- Is this for M-F only?
3- Do you accept delivery only during normal business hours?
4- Is your app in VB6 or VB.NET?
0
 
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 9702581
You need to be a little more specific.  =)

Are you going to round up the receive time to the nearest hour?...or use exact times?

What time are you assuming lunch is?  It does make a difference as you can't deliver during your lunch time...

Do you deliver on Weekends?... or does something received too late on Friday get delivered on Monday?
0
 
LVL 4

Expert Comment

by:brother7
ID: 9703117
In VB.NET, I think this function works:

--- Code Start ---

   Function CalcDeliveryTime(ByVal dropOffTime As DateTime) As DateTime
      Dim deliveryTime As DateTime
      Dim year, month, day, hour, minute, dayOfWeek As Integer

      year = dropOffTime.Year
      month = dropOffTime.Month
      day = dropOffTime.Day
      hour = dropOffTime.Hour
      minute = dropOffTime.Minute
      dayOfWeek = dropOffTime.DayOfWeek

      ' actual drop off time = Sat or Sun
      ' calculate as if drop off time is Mon @ 8 AM
      If dayOfWeek = 0 OrElse dayOfWeek = 6 Then
         If dayOfWeek = 0 Then
            deliveryTime = dropOffTime.AddDays(1)
         Else
            deliveryTime = dropOffTime.AddDays(2)
         End If
         year = deliveryTime.Year
         month = deliveryTime.Month
         day = deliveryTime.Day
         hour = 17
         minute = 0
         deliveryTime = New DateTime(year, month, day, hour, minute, 0)
         Return deliveryTime
      End If

      ' drop off time = Mon thru Fri
      If dayOfWeek >= 1 AndAlso dayOfWeek <= 4 Then
         If hour <= 8 Then
            hour = 17
            minute = 0
            deliveryTime = New DateTime(year, month, day, hour, minute, 0)
            Return deliveryTime
         End If
         If hour >= 17 And minute > 0 Then
            If dayOfWeek = 5 Then
               deliveryTime = dropOffTime.AddDays(3)
            Else
               deliveryTime = dropOffTime.AddDays(1)
            End If
            year = deliveryTime.Year
            month = deliveryTime.Month
            day = deliveryTime.Day
            hour = 17
            minute = 0
            deliveryTime = New DateTime(year, month, day, hour, minute, 0)
            Return deliveryTime
         End If

         If dayOfWeek = 5 Then
            deliveryTime = dropOffTime.AddDays(3)
         Else
            deliveryTime = dropOffTime.AddDays(1)
         End If
         If hour = 12 Then
            deliveryTime = deliveryTime.AddHours(1)
         End If
         Return deliveryTime
      End If

   End Function ' CalcDeliveryTime

--- Code End ---
0
 
LVL 2

Author Comment

by:CD-Softy
ID: 9703988
Sorry if I was a little vague...
VB6 is the chosen weapon, and times must be accurate...

All these variables are not fixed - ie the client can change the values of these variables in the set up part of my app. So, if they elect that the business hours are from Mon-Fri, then my app will store this info. They will also set the start and end time for each business day, as well as the lunch period.
This is  the simplified version of what I want (in reality, each day may have different starting & ending time, ie Friday) but once I got the basic formulae, I can apply it to each day.
This will mean that my app will store variables as...

'Obtained from user input via Configuration screen
sWeekDays = "MonFri"  
dStartTime = #08:00:00#
dEndTime = #17:00:00#
iLunchPeriod = 1   'Duration in Hrs
iDeliveryTarget = 8    'Duration in Hrs


So I guess that I would need a function like...

Private Function GetDueDateTime(ByVal dDateTimeReceived As Date, _
                                                 ByVal sWeekDays As String, _
                                                 ByVal dStartTime As Date, _
                                                 ByVal dEndTime As Date, _
                                                 ByVal iLunchPeriod as Interger, _
                                                 ByVal iDeliveryTarget As Interger) As Date

'This function to calculate the due date & time from the parameters

End Function

Hope this throws more light on my quest...

CD
0
 
LVL 4

Accepted Solution

by:
brother7 earned 50 total points
ID: 9705584
That's a lot of functionality to build into a single function.  I think the hardest part is to encapsulate the business hours informaton into a single structure, to allow easy access to the information.

I suggest a 7-element multidimensional array.  Each element would represent a day (0=Sun, 1=Mon, ... 6=Sat).  For each element, you would have a start time and end time.  Global variables would be lunch start time, lunch duration, and delivery target.

I think with the information presented in this way, it wouldn't be too hard to write the function.  I'm confident that I could do this in VB.NET.  I've already coded for the concrete case that you originally presented.  I'd just need to adjust the algorithm to make it more flexible.  Since I don't know any VB6, I'd have to lookup the corresponding functions in VB6... this will take me a while.  Would you like for me to try writing it in VB.NET.  I could probably write it in VB6, given enough time to look up the VB6 syntax.  Of course, more points is always a good incentive too :))
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 2

Author Comment

by:CD-Softy
ID: 9706609
brother7, great minds think alike.... this is the route i have started with.  My VB.NET is, well.....VB.NOT

Here's what I have so far...
All the settings are stored in a INI file, so we have...

[Day0]
IsWorkDay=TRUE
StartTime=0800
EndTime=1700
Lunch=60mins

[Day1]
IsWorkDay=TRUE
StartTime=0800
EndTime=1600
Lunch=45mins

etc
These elements will be loaded/saved into a UDT array, so you get
Day(0).IsWorkDay
Day(0).StartTime
Day(0).EndTime
etc

TimeRemaining is worked out from the time of the delivery to the end of the current day, and then from the start of the next working day up to the targeted time.

This all works fine, but I have now having trouble working out where the lunch time should be deducted - am or pm, sameday/ next day? etc.
This is turning out to be a nightmare.


CD
0
 
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 9706871
You need to make your lunch time's concrete just like your StartTime and EndTime.  Then you just have your day split into two parts, and you can use basically the same algorithm you have now.
0
 
LVL 4

Expert Comment

by:brother7
ID: 9707041
Can you please clarify... do you want code written in VB.NET or VB6?  I can produce a solution much easier in VB.NET than VB6.
0
 
LVL 2

Author Comment

by:CD-Softy
ID: 9748787
My appologies for the delay in replying....

I have managed to create the function to do what I want. I went down the route of calculating how many working minutes (excluding lunch) there are in each working day, then subtracting this from the target time in minutes. The final date & time can then be calculated from the start time which will give you the due time ;)

I wish to thank you all for your input on this. I will award brother7 50 points for assistance.

Moderators: please award brother7 (50) points for assistance and refund the remaining points.

CD
0
 

Expert Comment

by:shaun27612
ID: 10105632
CD-Softy can you share the function that you created? I am in the same situation as far as calculating elapsed time around work hours.

0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now