Originally Posted by
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
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:
Code:
ExpectedResult(Starting Date)
It would return your expected result.
Here is the code:
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:
Code:
? 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.