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

Improve company productivity with a Business Account.Sign Up

x
 
paaskyConnect With a Mentor 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
     
    FinishTime = DateAdd("h", Hours, StartingTime)
     
    ' 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

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

Paasky
0
 
arcusdCommented:
using DateAdd("h",8,#01/01/2000 10:00PM#)

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

hope this helps
0
 
PeterGBTAuthor 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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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

    FinishTime = DateAdd("h", Hours, StartingTime)
   
    ' 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

(add two hours)

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

(add eight hours)

(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,
Paasky
0
 
PeterGBTAuthor Commented:
Hey PAASKY

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
 
paaskyCommented:
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
   
    FinishTime = DateAdd("h", Hours, StartingTime)
   
    ' 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)

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

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?

I think you've just about earned your points!  Many thanks!
0
 
nealjdCommented:
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
 
PeterGBTAuthor 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
 
paaskyCommented:
Glad I was able to help.

About your extra questions, here are some samples:

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,
paasky
0
 
paaskyCommented:
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.

All Courses

From novice to tech pro — start learning today.