# Office Hours

I have a form where i am trying to limit a response to a date/time question to weekdays during office hours. I have the solution to the weekday side of this from a previous question, which is listed below. I am curious about how I could put in the time of day restraint as well. Any thoughts?

NOT(IN(WEEKDAY([Barrel Drop Off Date/Time]), LIST(1, 7)))

It would be a very similar expression. Care to take a stab at it?

Sure.

NOT(IN(HOUR([Barrel Drop Off Date/Time]), (15:00-09:00)))

I am trying to remember what I tried earlierâ€¦

On the right track (or one of 'em, at least):

• HOUR() requires a Duration as input rather than a DateTime, so you need to convert `[Barrel Drop Off Date/Time]` by subtracting a zero time from it: `HOUR([Barrel Drop Off Date/Time] - "00:00:00")`.

• HOUR() returns a Number rather than a Time, so youâ€™ll be working with 9, 10, 11, 12, 13, 14, 15, etc.

• IN() expects a list as the second argument, not a range, so the off-hours need to be expressed in a list: `IN(..., LIST(15, 16, 17, 18, 19, 20, 21, 22, 23, 0, 1, 2, 3, 4, 5, 6, 7, 8))`.

Which gives you:

``````NOT(
IN(
HOUR(
[Barrel Drop Off Date/Time]
- "00:00:00"
),
LIST(
15, 16, 17, 18, 19, 20, 21, 22, 23,
0, 1, 2, 3, 4, 5, 6, 7, 8
)
)
)
``````

That tests for times outside allowed hours. You could instead test for times within allowed hours:

``````IN(
HOUR(
[Barrel Drop Off Date/Time]
- "00:00:00"
),
LIST(9, 10, 11, 12, 13, 14)
)
``````

Or, if you want to allow for time constraints that fall off the top of the hour, you can use full times:

``````AND(
(TIME([Barrel Drop Off Date/Time]) >= "09:00:00"),
(TIME([Barrel Drop Off Date/Time]) < "15:00:00")
)
``````
1 Like

I tried entering the expressions mentioned in the same field as before, but it still allows time selections outside of the range be valid. Should I be entering this somewhere other than data validity?

Your expression should go in Valid if property for the Barrel Drop Off Date/Time column.

Youâ€™ll need to combine the weekday check and the hours check to get complete coverage.

``````AND(
NOT(IN(WEEKDAY([Barrel Drop Off Date/Time]), LIST(1, 7))),
(TIME([Barrel Drop Off Date/Time]) >= "09:00:00"),
(TIME([Barrel Drop Off Date/Time]) < "15:00:00")
)
``````

The day of the week still triggers the automated response, but Iâ€™ve been testing between 5pm-8pm and it doesnâ€™t throw the red flag.

Whoops! My expression was missing a comma. Iâ€™ve corrected it above. Please try the corrected expression.

That did it! Thank you so much!

1 Like