Solved

Working Day Function?

Posted on 2000-04-27
12
198 Views
Last Modified: 2008-03-17
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
Comment
Question by:PeterGBT
12 Comments
 
LVL 4

Expert Comment

by:arcusd
ID: 2754496
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
 

Author Comment

by:PeterGBT
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

by:paasky
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

    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
 

Author Comment

by:PeterGBT
ID: 2754693
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
 
LVL 10

Expert Comment

by:paasky
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
   
    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
 
LVL 10

Expert Comment

by:paasky
ID: 2754789
even the comment says ' calculate hours ...
it actually calculates minutes (so you will get exact time)
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:PeterGBT
ID: 2755764
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
 
LVL 10

Accepted Solution

by:
paasky earned 100 total points
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
     
    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
 

Expert Comment

by:nealjd
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

by:PeterGBT
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

by:paasky
ID: 2759628
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
 
LVL 10

Expert Comment

by:paasky
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
ms/acceee link a subform to a control 9 25
Autocomplete textbox in MS Access 2016 4 20
Help with SQl and UNION 3 16
Query 14 0
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now