Date Time

Assuming that the date-time is in Column A, here's the formula:
=WORKDAY(INT(A2),IF(OR(WEEKDAY(INT(A2),2)>5,+ISNUMBER(MATCH(INT(A2),Holidays,0)),(A2-INT(A2))>18/24),1,0),Holidays)+(IF((IF(OR(WEEKDAY(INT(A2),2)>5,+ISNUMBER(MATCH(INT(A2),Holidays,0)),D2>18/24),1,0))=1,6/24,IF((A2-INT(A2))<6/24,6/24,IF((A2-INT(A2))>18/24,6/24,(A2-INT(A2))))))

Now if you want to know how this formula came about. See the attached workbook that has the helper cells that put it together.

First of all you need an understanding on how Excel does date-times. A date is simply a whole number of days since Jan 1, 1900. The time of day is a fraction of the day, so 12 PM (noon) is 0.5 and 6 AM is 0.25 and 6 PM is 0.75. For the purposes of this exercise, I expressed these values as 6/24 and 18/24.

The DOW columns are gratuitous: they are not used in the calculations but the help to verify the results.

Column A contains the date-time to be evaluated.

The date time is separated to a Date in column C =INT(A2) (The whole number part)

And a time in column D =A2-INT(A2) (The fraction part)

The column called weekend has the formula =IF(OR(WEEKDAY(C2,2)>5,+ISNUMBER(MATCH(C2,Holidays,0)),D2>18/24),1,0).

The title is a bit misleading, but here is the logic. There are three things that can “bump” a time into the next day:
- The date time falls on a weekend
- The date time is on a holiday
- The date time is after 6 PM

This formula assigns a 1 if any of these conditions are met and a zero otherwise.

New Time has the formula =IF(E2=1,6/24,IF(D2<6/24,6/24,IF(D2>18/24,6/24,D2)))

Which says, if you got bumped to the next day, use 6 AM. Otherwise if before 6 AM use 6 AM or if greater than 6 PM use 6 AM (the next day – this is redundant given that we already took care of this case), or otherwise (later than 6 AM, earlier than 6 PM) just use the time.

New Date Time is =WORKDAY(C2,E2,Holidays)+F2
In other words, if E2 is 0, it’s the same day, if E2 is 1 then it’s the next work day. The holidays are a static named range (called Holidays) defined on Sheet2.

Take this simple formula and substitute back the parts such as C2 = INT(A2) so the formula now becomes: =WORKDAY(INT(A2),E2,Holidays)+F2. Keep substituting formulas until the formula is defined only in terms of column A. That gives you the hairy formula at the beginning of this post.

Personally, I like to stick with the helper columns since they are easier to read and debug.