Need help in adding hours to Date & time with Working hours condition

[navensg]

Hello All,



Need a help on the below condition.

Working hours : 9:00 AM to 5:00 PM

Example 1:
Starting Date : 8/1/2012 9:31 AM
Expected Result: 8/2/2012 5:00 PM

Example 2:
Starting Date : 8/3/2012 9:31 AM
Expected Result: 8/6/2012 5:00 PM

Example 3:
Starting Date : 8/1/2012 5:31 PM
Expected Result: 8/3/2012 5:00 PM

Example 4:
Starting Date : 8/1/2012 5:31 AM
Expected Result: 8/2/2012 5:00 PM


Basically if the Starting time falls between the working hours, then the expected result will go end of next business day. Also if the starting time falls in the weekend then technically Start time should begin from Monday 9:00 AM from there expected result will be Tuesday 5:00 PM.


Regards,
Naveen

[ser01]

Working hours : 9:00 AM to 5:00 PM

Example 1:
Starting Date : 8/1/2012 9:31 AM
Expected Result: 8/2/2012 5:00 PM

Basically if the Starting time falls between the working hours, then the expected result will go end of next business day. Also if the starting time falls in the weekend then technically Start time should begin from Monday 9:00 AM from there expected result will be Tuesday 5:00 PM.

There are many ways to accomplish this. Here is one:

IIF(Hour([Starting Date])<17 and Hour([Starting Date])>=9, [Expected Result]=format([Starting Date] +1,"MM/DD/YY 5:00 PM"), "")

Let us know how this works for you.

[navensg]

Thanks for the code, the Code you have given satisfy only one example, however i need one code which satisfy all the examples which i have mentioned(Ex: 1, 2, 3, 4).

Regards,
Naveen

[ser01]

Thanks for the code, the Code you have given satisfy only one example, however i need one code which satisfy all the examples which i have mentioned(Ex: 1, 2, 3, 4).

Regards,
Naveen

I wrote a function for you that should do what you need. The function has one parameter, which is the date the needs to be processed. It returns the expected result date/time. Insert the code in a standard module. After that, the function can be used anywhere like this:

ExpectedResult(Starting Date)

It would return your expected result.
Here is the code:

Public Function ExpectedResult(datMyDate As Date)

    Dim iHours As Integer
    Dim iHour As Integer
    Dim iDay As Integer
    Dim iAdd As Integer
    Dim datResult As Date


    iHour = Hour(datMyDate)
    iDay = Weekday(datMyDate)


    Select Case iDay
    Case 1 To 4
        iAdd = 1
    Case 5 To 7
        iAdd = 7 + 2 - iDay
        ExpectedResult = Format(datMyDate + iAdd + 0.5, "MM/DD/YYYY 5:00 AMPM")
        Exit Function
    End Select


    Select Case iHour
    Case 0 To 16
        iAdd = iAdd + 0
    Case 17 To 23
        iAdd = iAdd + 1
        ExpectedResult = Format(datMyDate + iAdd, "MM/DD/YYYY 5:00 AMPM")
        Exit Function
    End Select
    ExpectedResult = Format(datMyDate + iAdd + 0.5, "MM/DD/YYYY 5:00 AMPM")
    
End Function

Please NOTE, there is no error handling including, so you will have to add your own.

Here are some examples of how the function performs from the Immediate Window:

? ExpectedResult(#8/1/2012 9:31 AM#)
08/02/2012 5:00 PM
? ExpectedResult(#8/3/2012 9:31 AM#)
08/06/2012 5:00 PM
? ExpectedResult(#8/1/2012 5:31 PM#)
08/03/2012 5:00 PM
? ExpectedResult(#8/1/2012 5:31 AM#)
08/02/2012 5:00 PM


? ExpectedResult(#8/1/2012 11:31 AM#)
08/02/2012 5:00 PM
? ExpectedResult(#8/5/2012 10:31 AM#)
08/06/2012 5:00 PM
? ExpectedResult(#8/1/2012 7:31 PM#)
08/03/2012 5:00 PM
? ExpectedResult(#8/1/2012 7:31 AM#)
08/02/2012 5:00 PM

Please post back and let us know if this solves your problem.
Cheers.

[navensg]

Thanks Much, its solved my problem