Google Sheets – Data Validation Rule for First Day of the Month

data validationgoogle sheets

I have a Google Sheet in which users key in records for a specific month. A report on another tab looks up the month and consolidates it. I'm trying to enforce using the first day of the month to match the header in the report sheet. (I know I could replace the lookup with a sumif range, but I'm trying to keep the formulas as short as possible and the amount of calculation to a minimum, since this ends up being a large document)

I've set a Data Validation rule to enforce this rule, but I just looked and found numerous instances of violations, which make the report inaccurate.
The rule is: =eomonth([cell],-1)+1

Mousing over the cell shows me the validation text, but the rule is set to reject the value completely! What is going on wrong here?

enter image description here

Best Answer

Custom formula type of data validation is evaluated independently of the cell. In other words, I was assuming that it was checking if the value returned by the expression was equal to the current cell. In reality, it was checking to see if =eomonth(A169,-1)+1 equaled "true" (or -1, I suppose).

There are 2 potential solutions here.

  1. Maintain "Custom formula" as the type, but change the formula to: =A169=eomonth(A169,-1)+1

  2. Change the validation type to "Date" and set it to Date equal to: =eomonth(A168,-1)+1

Related Topic