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)))
Solved! Go to Solution.
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")
)
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")
)
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!
User | Count |
---|---|
43 | |
32 | |
25 | |
23 | |
14 |