VBA Check Time excluding hours

**wambaugh** · 05-23-2015, 05:00 AM

Guys

is there a way of adding time to a date/time with exclusions?

I need to add a number of hours to a date/time which I do using DateAdd

However if that time is between 5pm and 730 am the next morning I need the date returned to be the following day and time to be plus the reminder

For instance if the date time is cell A1 is "10/04/2015 16:00:00" then this plus three hours would be "10/04/2015 19:00:00" The code I use is

d = DateAdd("h", 3, Worksheets("Sheet1").Range("A1").Value)

The date I need it to come back with is "11/04/2015 09:30:00" as anything between 1700 and 0730 should be disallowed.

Any ideas?

Mark

**6StringJazzer** · 05-23-2015, 09:08 AM

What are the assumptions about what the start time will be? Suppose the start time is 05:30? Is the correct result 08:30? Or 10:30?

Here is a solution for the case you describe, generalized for adding any number of hours. I used arithmetic on the time instead of DateAdd.

The start time is assumed to be after 07:30 and before 17:00. If your cases are more complex, you need to provide more detail.

Please Login or Register  to view this content.

**wambaugh** · 05-23-2015, 12:17 PM

Thank you so much

can I modify it for different Start times as I forgot about weekends

as it runs it stops at 5pm and re starts at 730am which is fine

If the date supplied is on a Friday then the Stop time is still 5pm but the restart time is the following Monday at 730am

I suppose I could just add 2 days to the result unless there is a better way.

Mark