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

[naveensg]

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​

 

[plog]

Macros aren't the greast thing in the world, so how about a function?

Function getEndTime(BT)
    ' takes beginning date/time and determines end time, BT is datetime value
bdate = DateValue(BT)
bhour = Hour(TimeValue(BT))
    ' seperates out hours and date from BT
 
If bhour >= 17 Then bdate = DateAdd("d", 1, bdate)
    ' if after 5:00 pm moves begin date to next day
 
adddays = 1
    ' number of days after begin date to make end date
If Weekday(bdate) = 6 Or Weekday(bdate = 7) Then adddays = 3
If Weekday(bdate) = 1 Then adddays = 2
    ' moves end date to account for weekends
 
getEndTime = DateAdd("d", adddays, bdate) & " 5:00:00 PM"
End Function

Paste the above code into a module. Then to use it in a query you would do this:

EndDateTime: getEndTime([NameOfStartDateTimeFieldHere])

Or in a form or report you would do this:

=getEndTime([NameOfStartDateTimeFieldHere])

Be sure to replace NameOfStartDateTimeFieldHere with the actual name of your field.

 

[naveensg]

Hello Sir,

Thanks for the Code, however it's not working in all conditions. Its considering the latest If Condition to calculate End Time.

Regards,
Naveen

 

[plog]

I don't understand what you mean. Could you post some sample data that causes it to return the incorrect result? Also give me what it should return.