0

I have a custom list that I'm trying to restrict data entry for valid day of week and time. My current column validation works for day of week being Monday, Wednesday or Friday. It looks like this:

=CHOOSE(WEEKDAY([Requested date for approval]),FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE)

I'm trying to figure out the syntax to add that it also has to be between 8 am and 12:00 pm on those days.

Any help would be greatly appreciated.

Robbert
  • 6,481
  • 5
  • 35
  • 61
Steve Morley
  • 101
  • 1

1 Answers1

0

You would use an AND statement to include a second criteria

=AND(CHOOSE(WEEKDAY([Requested date for approval]),FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE),
  AND(
    [Requested date for approval]-INT([Requested date for approval])*24 >= 8,
    [Requested date for approval]-INT([Requested date for approval])*24 <= 24
  )
)

I confess, I've never heard of the CHOOSE function, but the time calculation is based on the information at Microsoft

Convert times To convert hours from the standard time format to a decimal number, use the INT function.

Column1       Formula                           Description (possible result)
10:35 AM      =([Column1]-INT([Column1]))*24    Number of hours since 12:00 AM (10.583333)
12:15 PM      =([Column1]-INT([Column1]))*24    Number of hours since 12:00 AM (12.25)

EDIT

To calculate the day of the week, you can use the TEXT function to return the day of the week (i.e. Monday)

=TEXT(WEEKDAY([ColumnName]), "dddd")

It won't be pretty, but you can use a series of AND logical operators

=AND(
  TEXT(WEEKDAY([Requested date for approval]), "dddd") = "Monday",
  AND(
    TEXT(WEEKDAY([Requested date for approval]), "dddd") = "Wednesday",
    AND(
      TEXT(WEEKDAY([Requested date for approval]), "dddd") = "Friday",
      AND(
        [Requested date for approval]-INT([Requested date for approval])*24 >= 8,
        [Requested date for approval]-INT([Requested date for approval])*24 <= 24
      )
    )
  )
)

Posting Working Solution

=IF(
  AND(
    CHOOSE(
      WEEKDAY([Requested date for approval]),FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE
    ),
    ([Requested date for approval]-INT([Requested date for approval]))*24>=8,
    ([Requested date for approval]-INT([Requested date for approval]))*24<=12
  ),
  TRUE
)
Robbert
  • 6,481
  • 5
  • 35
  • 61
  • So it looks like my choose statement for the day of the week which came from here (http://sharepoint.stackexchange.com/questions/136423/column-validation-for-day-monday-or-friday) and the time validation, don't seem to play well together. No date and time combination will work. You said you had never heard of "choose", what would use instead of that for the day of the week? – Steve Morley Apr 28 '15 at 12:13
  • Thanks, it does complain of a syntax error, but it's great starting point. – Steve Morley Apr 28 '15 at 14:17
  • I'm sure there's a missing parenthesis somewhere. The risks you take when writing untested code in a non native environment :) – Robbert Apr 28 '15 at 14:35
  • Well, I see the biggest issue is too many equal signs. There should only be the one at the beginning. – Robbert Apr 28 '15 at 16:16
  • This is what we actually came up with as a solution. Couldn't have done it without your help: =IF(AND(CHOOSE(WEEKDAY([Requested date for approval]),FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE),([Requested date for approval]-INT([Requested date for approval]))*24>=8,([Requested date for approval]-INT([Requested date for approval]))*24<=12),TRUE) – Steve Morley Apr 28 '15 at 18:25