[Solved] Data Validity on a Date
-
- Posts: 4
- Joined: Thu Apr 23, 2020 9:23 pm
[Solved] Data Validity on a Date
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!
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!
Last edited by MrProgrammer on Fri Apr 24, 2020 5:10 pm, edited 2 times in total.
Reason: Tagged ✓ [Solved]
Reason: Tagged ✓ [Solved]
OpenOffice 4.1.7
Re: Data Validity on a Date
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.
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.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
-
- Posts: 4
- Joined: Thu Apr 23, 2020 9:23 pm
Re: Data Validity on a Date
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.
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.
OpenOffice 4.1.7
Re: Data Validity on a Date
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?
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Data Validity on a Date
What's wrong?
- Attachments
-
- t101752.ods
- (13.07 KiB) Downloaded 112 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
- MrProgrammer
- Moderator
- Posts: 4905
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Data Validity on a Date
bobgilbert wrote: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.
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. 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.FJCC wrote: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?
[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Data Validity on a Date
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.bobgilbert wrote: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.
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/
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
-
- Posts: 4
- Joined: Thu Apr 23, 2020 9:23 pm
Re: Data Validity on a Date
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.MrProgrammer wrote:bobgilbert wrote: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.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. 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.FJCC wrote: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?
[Tutorial] Ten concepts that every Calc user should know
Thanks to everyone for their responses!
OpenOffice 4.1.7
Re: Data Validity on a Date
+1:
The custom validity with a Formula works fine in my LibreOffice 6.2.8:
The custom validity with a Formula works fine in my LibreOffice 6.2.8:
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
-
- Posts: 4
- Joined: Thu Apr 23, 2020 9:23 pm
Re: Data Validity on a Date
This did take care of my issue, thanks!MrProgrammer wrote:bobgilbert wrote: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.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. 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.FJCC wrote: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?
[Tutorial] Ten concepts that every Calc user should know
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!
OpenOffice 4.1.7