[Solved] Data Validity on a Date

[bobgilbert]

I am trying to setup Data Validity that only allows the user to type in the 11th or 26th of a month as a date in cell A1 (i.e. they type in 4/11/2020 or 5/26/2020 or 3/11/21, etc)

I have tried everything I can think of but just have not been able to figure out a way to do this in Calc (it is quite easy in Excel).

I am aware that I could use a dropdown box for the "Day" part of the date and only allow them to select the number 11 or 26 but I am trying to avoid that if possible because it creates additional complications with what I am trying to do.

Thanks in advance for your time!

[RusselB]

Welcome to the Forums.
One problem with what you are attempting to do is due to the different date formats that are used.
To use your example of 4/11/2020, is that April 11, 2020 or November 4, 2020?
In order to, possibly, get around this, I would suggest incorporating the DAY function into your Data Validity setup.
In this way, it the DAY portion of the date can be referenced easily and accurately.
Please don't make the mistake of assuming what you can do in Excel can be done in OpenOffice Calc. There are situations where Excel has commands and functions that OpenOffice Calc does not.
One option you might want to consider, is using LibreOffice Calc, which is very similar to OpenOffice Calc, but also has better compatibility with Excel.

[bobgilbert]

Thanks for the response, Russell!

I have actually incorporated the Day command in my Excel versions, for this example lets pretend I have the formula =DAY(A1) in cell B1 but I still have been unable to get Data Validation to work the way I am attempting to.

I am very open to Libre in concept, but we already have OpenOffice installed on numerous machines and switching over would be a significant project.

[FJCC]

I can't think of an general way to do this. If the dates of interest are limited to a span of years, say the current year +/- 3, then a cell range could be made containing the valid values. Is that a possible solution?

[Villeroy]

What's wrong?

[MrProgrammer]

I am trying to setup Data Validity that only allows the user to type in the 11th or 26th of a month as a date in cell A1.

If the dates of interest are limited to a span of years, say the current year +/- 3, then a cell range could be made containing the valid values. Is that a possible solution?

Sample of that idea. There are only 2400 valid dates this century. For a modern computer 2400 is a small number. The attached file is tiny. It was created in OpenOffice.

202004231628.ods

(24.25 KiB) Downloaded 95 times

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
[Tutorial] Ten concepts that every Calc user should know

[RusselB]

I am very open to Libre in concept, but we already have OpenOffice installed on numerous machines and switching over would be a significant project.

The "significant" part of the project, switching from OpenOffice to LibreOffice, is just a matter of downloading and installing LibreOffice onto the machine(s) that you want it on.
If you don't want to do the full installation, or you just want to give LibreOffice a try, there is a portable version that you can get from https://www.libreoffice.org/download/portable-versions/

[bobgilbert]

I am trying to setup Data Validity that only allows the user to type in the 11th or 26th of a month as a date in cell A1.

If the dates of interest are limited to a span of years, say the current year +/- 3, then a cell range could be made containing the valid values. Is that a possible solution?

Sample of that idea. There are only 2400 valid dates this century. For a modern computer 2400 is a small number. The attached file is tiny. It was created in OpenOffice.

202004231628.ods

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
[Tutorial] Ten concepts that every Calc user should know

I will play with this at the office tomorrow, great idea! I will mark this as solved tomorrow of this takes care of my problem.

Thanks to everyone for their responses!

[Zizi64]

+1:

The custom validity with a Formula works fine in my LibreOffice 6.2.8:

[bobgilbert]

I am trying to setup Data Validity that only allows the user to type in the 11th or 26th of a month as a date in cell A1.

If the dates of interest are limited to a span of years, say the current year +/- 3, then a cell range could be made containing the valid values. Is that a possible solution?

Sample of that idea. There are only 2400 valid dates this century. For a modern computer 2400 is a small number. The attached file is tiny. It was created in OpenOffice.

202004231628.ods

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
[Tutorial] Ten concepts that every Calc user should know

This did take care of my issue, thanks!

Thanks to EVERYONE for your responses. if I didn't implement the idea, it does NOT mean I did not appreciate the thought!

It is so kind of everyone on here to help people out, thank you so much and take care!