On a spreadsheet I have a cell where the user inputs a period ending date which should always fall on a Sunday. The format for that cell is set for Date, 31 Dec 1999. The validation rule for that cell:
Allow: Date
Data: Equal
Value: WEEKDAY(J4,1) = 1 (the 2nd parameter is correct for my locale)
No matter what date is entered, the input is considered invalid. This is working just fine with Excel. I have no idea what’s going on. Anyone?
Cheers!
Image in response to Mike’s reply:
And the cell in question is J4, right? Also what do you enter in the cell (an example)? And what happens if you enter the same data into another cell (say, K4) without the validity check, and then use =ISNUMBER(K4) in L4?
Let me doubt your statement that “This is working just fine with Excel”
The fact is that WEEKDAY (J4,1) = 1 is a logical expression that will return only 0 or 1, FALSE or TRUE. And the condition “Date equals” implies a comparison with the date.
Do you need a date from a specific range? For example, all Sundays in the last month? For the next month? For the entire current year? Or do you want to give the opportunity to enter any date, any year of any century, but so that it is only Sunday? There are various ways to organize data validation for these cases.
@JohnSUN - nice catch! I had overlooked the first two lines there, and of course, what I overlooked was the real problem! “Custom” validation rule seems to be needed instead.
@mikekaganski Possible solutions could be a “cell range” or a list formed by a macro, or processing the entered value with a macro in the “Error Allert”.
Perhaps data validation is not needed here at all. Since this is the “end of the period”, it is likely that the sheet has a “beginning of the period” and, possibly, some indication of the length of the period. In this case, @MJ12 can simply calculate which Sunday the period will end.
The user can enter whatever date they like, the validation rule makes sure the date entered is a Sunday. I’m adding an image to the original post in response to Mike’s reply. I need to dual boot into Windoze 10 and spin up Excel to make sure that validation is indeed working in Excel.
@MJ12: the first comment by @JohnSUN shows you the problem; no other data needed. It was in these lines:
Allow: Date
Data: Equal
Because it should had been
Allow: Custom
Formula: ...Once again, is this cell is “end of the period”? So the user has already entered the “start of the period” somewhere? That is, the main condition is “the input value is not less than the start date”, right? Is “day of the week equal to Sunday” the second condition?
As always, @JohnSUN looks not only to the question asked, but also wider, to the essence of the problem: if OP had organized the data in most optimal/logical way. So to clarify things:
Sorry… the starting date is calculated from the ending date. I know it makes little sense, that’s the way they want it. The Validity dialog has no “Allow: Custom” selection in the dropdown. I’m running LibreOffice v. 6.0.7.3. This page shows the options that are available in the dropdown list: Criteria
Let’s try to solve this task. The first problem is “The user can enter whatever date they like.” Is it really impossible to limit this value at all? Even if it is even 100 values (Sunday for a year and a half), then from them we can make a list for selection.
I’m running LibreOffice v. 6.0.7.3.
Any reason to use that outdated version? Custom formula was introduced for 6.2, which was released more than a year ago.
And btw, its documentation is still missing (as of today’s master): tdf#130738
No reason, just an oversight. I’m new to Linux and LibreOffice. Cheers for everyone’s help!
An alternative solution is that you can change anyone entered date by moving it to the nearst Sunday automatically (for example, from Monday-Tuesday-Wednesday to the previous Sunday, the rest of the days to the next Sunday). This will require a simple macro.
@JohnSUN Thanks for your advice! Once I installed version 6.4.3.2 of LO and selected the “Custom” option for the validation rule, the WEEKDAY() function works a treat.
That was not my advice, this was advised by Mike Kaganski 
So, if do you wish to allow in a cell only dates that fall on Sunday:
Data - Validity…, Criteria tabAllow select Custom
Formula type WEEKDAY(J4;1)=1 (without an inicial “=”)
OKThanks to @mikekaganski and @JohnSUN.
There is a bug report for the lack of documentation in the LibreOffice Help.