Ask Your Question

LibreOffice Calc WEEKDAY() function

asked 2020-05-18 03:34:17 +0200

MJ12 gravatar image

updated 2020-05-18 08:22:22 +0200

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?


Image in response to Mike's reply:

image description

edit retag flag offensive 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?

Mike Kaganski gravatar imageMike Kaganski ( 2020-05-18 07:03:01 +0200 )edit

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.

JohnSUN gravatar imageJohnSUN ( 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.

JohnSUN gravatar imageJohnSUN ( 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.

Mike Kaganski gravatar imageMike Kaganski ( 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.

JohnSUN gravatar imageJohnSUN ( 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.

MJ12 gravatar imageMJ12 ( 2020-05-18 08:21:19 +0200 )edit

@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: ...
Mike Kaganski gravatar imageMike Kaganski ( 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?

JohnSUN gravatar imageJohnSUN ( 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.
Mike Kaganski gravatar imageMike Kaganski ( 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. This page shows the options that are available in the dropdown list:

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

1 Answer

Sort by » oldest newest most voted

answered 2020-06-19 02:25:40 +0200

LeroyG gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-05-18 03:34:17 +0200

Seen: 107 times

Last updated: Jun 19