# Calculate Time Due From a DateTime value in business intervals

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
LVL 2
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
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?
High School Computer Science, Computer Applications, and Mathematics TeachersCommented:
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?
Commented:
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 ---
Author Commented:
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
Commented:
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 :))

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
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
High School Computer Science, Computer Applications, and Mathematics TeachersCommented:
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.
Commented:
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.
Author Commented:
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
Commented:
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.

###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.