Solved

# Working Day Function?

Posted on 2000-04-27
203 Views
I'm stuck! I run an Access97 database that controls the ordering and processing of telecoms. circuits. Everything is fine, but i now have to provide some kind of "jeopody management".

OK, here goes: We have 8 hours in which to provide each circuit. A working day for our engineers runs from 8 am to 5 pm,(no lunch breaks). I already timestamp when we append the order to the database, and my
intention is to be able to flag up circuits that are still in build but nearing the 8 hour deadline. My order queue form refreshes itself every 30 minutes, thereby showing any new orders, ordered by append time, the oldest being at the top of the list.  But my problem is working out a function that will only use the working hours in which to base how long we have left before the circuit must go live.

Orders are put on the db throughout the whole day, so any given order's 8 hours will be formed by (part of day one) + (part of day two), unless of course it happens to be appended in the first hour of the day i.e. betweens 8 AM.

So, given a start time, i.e. the date/time the order was appended, how do i calculate the target finish time i.e 8 working hours ahead?
0
Question by:PeterGBT
[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

LVL 4

Expert Comment

ID: 2754496

will return a date/time #01/02/2000 6:00:00 AM#

hope this helps
0

Author Comment

ID: 2754554
Thanks arcusd

That's a great start.  I presume i will now have to use either a SELECT CASE, or an IF THEN routine to establish whether the inputted time is before 9AM.  If it is, the returned time in the same working day is valid and OK, if it isnt, take away the time period for the first day from 8 hours, and add the difference to 8AM the next day, e.g.:

Inputted time = 3PM 01/01/2000.   3PM - 5PM = 2 hours, 8 hours less 2 hours leaves 6 hours, therefore Return time = 2PM 02/01/2000.

This is my problem. Any ideas would be gratefully recd.
0

LVL 10

Expert Comment

ID: 2754568
Hello PeterGBT,

Here's my suggestion:

Public Function FinishTime(StartingTime As Date, Hours As Double, WorkStartTime As Date, WorkEndTime As Date) As Date
Dim NextDayHours As Double

' calculate hours that will be moved to next day
NextDayHours = DateDiff("n", CDate(Format\$(FinishTime, "mm/dd/yyyy") & " " & Format\$(WorkEndTime, "hh:mm")), FinishTime)

' move remaining hours to next day
If NextDayHours > 0 Then
FinishTime = DateAdd("n", NextDayHours, CDate(Format\$(StartingTime + 1, "mm/dd/yyyy") & " " & Format\$(WorkStartTime, "hh:mm")))
End If

End Function

Usage examples (note I've European date format settings):

? FinishTime(Now,2,"01.01.2000 8:00","01.01.2000 17:00")
27.4.2000 14:44:25

? FinishTime(Now,8,"01.01.2000 8:00","01.01.2000 17:00")
28.4.2000 11:43:00

(Current time here 27.4.2000 12:44)

I hope this is what you're looking for. Please let me know if this is not working with your regional settings.

Regards,
0

Author Comment

ID: 2754693

That's really cool!  Thank you for your prompt reply.  Now to the other part of the problem.  Saturdays and Sundays are not engineers working days, but it is possible that orders will be entered on these days so i need the funtion to do the following:

1) If the order is entered on a Friday, next day = Monday.

2) If the order is entered on a Saturday or Sunday, next day  = Tuesday.  i.e. the function "thinks" the order was entered on the Monday.

Hope i am making myself clear.
0

LVL 10

Expert Comment

ID: 2754783
Okay, I made little modifications to my function:

Public Function FinishTime(StartingTime As Date, Hours As Double, WorkStartTime As Date, WorkEndTime As Date) As Date
Dim NextDayHours As Double

' if the starting time is in Saturday or Sunday, change it to Monday
If WeekDay(StartingTime, vbMonday) > 5 Then
StartingTime = StartingTime + 8 - WeekDay(StartingTime, vbMonday)
End If

' calculate hours that will be moved to next day
NextDayHours = DateDiff("n", CDate(Format\$(FinishTime, "dd.mm.yyyy") & " " & Format\$(WorkEndTime, "hh:mm")), FinishTime)

' move remaining hours to next day
If NextDayHours > 0 Then
FinishTime = DateAdd("n", NextDayHours, CDate(Format\$(StartingTime + 1, "dd.mm.yyyy") & " " & Format\$(WorkStartTime, "hh:mm")))

' If finishing time is Saturday or Sunday, move it to Monday
If WeekDay(FinishTime, vbMonday) > 5 Then
FinishTime = FinishTime + 8 - WeekDay(FinishTime, vbMonday)
End If
End If

End Function

usage examples:

? FinishTime(Now+2,2,"01.01.2000 8:00","01.01.2000 17:00")
1.5.2000 16:20:28

? FinishTime(#04/29/2000 15:00:00#,8,#01/01/2000 08:00:00#,#01/01/2000 17:00:00#)
2.5.2000 14:00:00

Note that you may need to adjust format mask "dd.mm.yyyy" to work with your date format system.

Getting closer now? :o)

0

LVL 10

Expert Comment

ID: 2754789
even the comment says ' calculate hours ...
it actually calculates minutes (so you will get exact time)
0

Author Comment

ID: 2755764

Hey, it works great.  I only had to change the dot separators in the dates to forward slashes, and it operates perfectly.  One last point.  I must be really thick today, but when the starting time moves forward to the Monday, how do i adjust the time to a default 8 AM?

0

LVL 10

Accepted Solution

ID: 2755884
I was almost sure you're going to ask that earlier... :o)

Here you go:

Public Function FinishTime(StartingTime As Date, Hours As Double, WorkStartTime As Date, WorkEndTime As Date) As Date
Dim NextDayHours As Double

' if the starting time is in Saturday or Sunday, change it to Monday
If WeekDay(StartingTime, vbMonday) > 5 Then
StartingTime = CDate(Format\$(StartingTime, "dd.mm.yyyy") & " " _
& Format\$(WorkStartTime, "hh:mm")) + 8 - WeekDay(StartingTime, vbMonday)
End If

' calculate hours that will be moved to next day
NextDayHours = DateDiff("n", CDate(Format\$(FinishTime, "dd.mm.yyyy") _
& " " & Format\$(WorkEndTime, "hh:mm")), FinishTime)

' move remaining hours to next day
If NextDayHours > 0 Then
CDate(Format\$(StartingTime + 1, "dd.mm.yyyy") _
& " " & Format\$(WorkStartTime, "hh:mm")))

' If finishing time is Saturday or Sunday, move it to Monday
If WeekDay(FinishTime, vbMonday) > 5 Then
FinishTime = FinishTime + 8 - WeekDay(FinishTime, vbMonday)
End If
End If

End Function

Example:
? FinishTime(#04/29/2000 15:00:00#,8,#01/01/2000 08:00:00#,#01/01/2000 17:00:00#)
1.5.2000 16:00:00

0

Expert Comment

ID: 2756741
Function Find_Date(DateEntered)

Dim AM8, PM5, OffSet, StartHour As Date
Dim StartDate, TargetDate As Date

AM8 = 8 / 24
PM5 = 17 / 24

StartDate = Int(DateEntered)
StartHour = DateEntered - StartDate

OffSet = StartHour - AM8

If OffSet > 0 Then
StartDate = StartDate + 1
Rem Check for holidays, weekends, etc and bump up to next work day : each +1 adds 1 day
TargetDate = StartDate + OffSet + AM8
End If
If OffSet <= 0 Then TargetDate = StartDate + OffSet + PM5

Find_Date = TargetDate

End Function

0

Author Comment

ID: 2759153
Many thanks to both paasky and nealjd.

Paasky, you've got the points.  What you wrote did exactly as i wanted, and i must admit, i'm still working out how it all works.

I am familiar, but not expert in the Format and Cdate functions, so tell me, so i can develop this further, how for instance, i would write an If-Then statement that said:

If the input time is after 5PM, Then .............

OR

If the input time is Between 5PM and 8AM, Then.....

Many thanks

Peter Gidden

0

LVL 10

Expert Comment

ID: 2759628
Glad I was able to help.

If Format(SomeDate,"hh:nn") > #17:00# then ...

If Format(SomeDate,"hh:nn") <= #08:00# OR Format(SomeDate,"hh:nn") >= #17:00# then ...

(you want to check if the time is not in working hours, eh?)

We don't use AM's and PM's here were I live, but I assume #05:00PM# would work as well as #17:00# with my settings...

Best regards,
0

LVL 10

Expert Comment

ID: 2759642
oh my... I just noticed minute formatting bug in my earlier function! You should replace hh:mm to hh:nn. Anyway I'm glad I noticed before your client. :-)

Format\$(WorkEndTime, "hh:mm")), FinishTime)

should be

Format\$(WorkEndTime, "hh:nn")), FinishTime)

and

Format\$(WorkStartTime, "hh:mm")))

should be

Format\$(WorkStartTime, "hh:nn")))

In Access: nn=minutes, mm=months

0

## Featured Post

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Itâ€™s been over a monthÂ into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targetiâ€¦
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all recordâ€¦
Whatâ€™s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macroâ€™s, and VBA code.
###### Suggested Courses
Course of the Month7 days, 9 hours left to enroll