# Working Day Function?

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?
###### Who is Participating?

x

Commented:
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

Commented:

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

hope this helps
0

Author Commented:
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

Commented:
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 Commented:

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

Commented:
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

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

Author Commented:

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

Commented:
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 Commented:
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

Commented:
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

Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.