Solved

Working Day Function?

Posted on 2000-04-27
12
200 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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
 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

770 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