Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Calculate Time Due From a DateTime value in business intervals

Posted on 2003-11-07
Medium Priority
688 Views
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
Question by:CD-Softy
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 3
• 2
• +1

LVL 4

Expert Comment

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 86

Expert Comment

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

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
Else
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
Else
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
Else
End If
If hour = 12 Then
End If
Return deliveryTime
End If

End Function ' CalcDeliveryTime

--- Code End ---
0

LVL 2

Author Comment

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

brother7 earned 200 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

LVL 2

Author Comment

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 86

Expert Comment

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

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

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

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate rowâ€¦
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â€¦
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can launâ€¦
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â€¦
###### Suggested Courses
Course of the Month11 days, 21 hours left to enroll