# LibreOffice Calc WEEKDAY() function

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:

edit retag close merge delete

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?

( 2020-05-18 07:03:01 +0200 )edit
2

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.

( 2020-05-18 07:23:39 +0200 )edit

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.

( 2020-05-18 07:37:18 +0200 )edit

@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.

( 2020-05-18 07:48:34 +0200 )edit

@Mike Kaganski 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.

( 2020-05-18 08:18:09 +0200 )edit

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.

( 2020-05-18 08:21:19 +0200 )edit
1

@MJ12: the first comment by @JohnSUN shows you the problem; no other data needed. It was in these lines:

Allow: Date
Data: Equal


Allow: Custom
Formula: ...

( 2020-05-18 08:28:28 +0200 )edit

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?

( 2020-05-18 08:29:43 +0200 )edit

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:

1. Why it doesn't work when you expect it to work: see my comment 5 minutes ago - which is what @JohnSUN mentioned first.
2. If it is done in the best possible way: answer @JohnSUN's additional questions.
( 2020-05-18 08:36:03 +0200 )edit

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: https://help.libreoffice.org/6.1/en-U...

( 2020-05-18 08:37:50 +0200 )edit

Sort by » oldest newest most voted

So, if do you wish to allow in a cell only dates that fall on Sunday:

• select that cell (in this question is J4)
• open menu Data - Validity…, Criteria tab
• for Allow select Custom
• and in Formula type WEEKDAY(J4;1)=1 (without an inicial "=")
• OK

Thanks to @Mike Kaganski and @JohnSUN.

There is a bug report for the lack of documentation in the LibreOffice Help.

more

## Stats

Seen: 107 times

Last updated: Jun 19