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)))

Solved Solved
0 8 679
1 ACCEPTED 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")
)

View solution in original post

8 REPLIES 8

Steve
Participant V

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!

Top Labels in this Space