validate date column with AND and Today()

Question

I want my "Day" column to validate as such:

• It can only be within 14 days in the past
• It can only be a past date or current day
• It can not be before June 21
• It can not be not be after August 26.

This is a limited time fitness tracking app - so I don't want them to track too far in the past, any future date, and within the dates of the program.

=AND([Day]>TODAY()-14,[Day]<=TODAY(),[Day]>6/21/2017,[Day]<8/26/2017)

I have tried the above. Syntax is correct as it does not error but choosing today's date of June 22, 2017 is not validating when I figure it should.

What am I missing? Thanks, Maureen

Answer 1

I think I got it. I should have been using this:

=AND(Day>TODAY()-14,Day<TODAY()+1,Day>datevalue("6/21/2017"),Day<datevalue("8/26/2017"))