[Solved] Data Validity on a Date

Discuss the spreadsheet application

[Solved] Data Validity on a Date

Postby bobgilbert » Thu Apr 23, 2020 9:31 pm

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!
Last edited by MrProgrammer on Fri Apr 24, 2020 5:10 pm, edited 2 times in total.
Reason: Tagged ✓ [Solved]
OpenOffice 4.1.7
bobgilbert
 
Posts: 4
Joined: Thu Apr 23, 2020 9:23 pm

Re: Data Validity on a Date

Postby RusselB » Thu Apr 23, 2020 10:57 pm

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.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
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.
User avatar
RusselB
Moderator
 
Posts: 6108
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Data Validity on a Date

Postby bobgilbert » Thu Apr 23, 2020 11:04 pm

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.
OpenOffice 4.1.7
bobgilbert
 
Posts: 4
Joined: Thu Apr 23, 2020 9:23 pm

Re: Data Validity on a Date

Postby FJCC » Thu Apr 23, 2020 11:05 pm

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?
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7748
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Data Validity on a Date

Postby Villeroy » Thu Apr 23, 2020 11:36 pm

What's wrong?
Attachments
t101752.ods
(13.07 KiB) Downloaded 27 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28544
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Data Validity on a Date

Postby MrProgrammer » Thu Apr 23, 2020 11:53 pm

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.
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?
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 25 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
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3960
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Data Validity on a Date

Postby RusselB » Fri Apr 24, 2020 1:25 am

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.

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/
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
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.
User avatar
RusselB
Moderator
 
Posts: 6108
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Data Validity on a Date

Postby bobgilbert » Fri Apr 24, 2020 1:52 am

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.
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?
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!
OpenOffice 4.1.7
bobgilbert
 
Posts: 4
Joined: Thu Apr 23, 2020 9:23 pm

Re: Data Validity on a Date

Postby Zizi64 » Fri Apr 24, 2020 6:52 am

+1:

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

Custom Validity.png
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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.
User avatar
Zizi64
Volunteer
 
Posts: 9540
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Data Validity on a Date

Postby bobgilbert » Fri Apr 24, 2020 4:39 pm

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.
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?
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!
OpenOffice 4.1.7
bobgilbert
 
Posts: 4
Joined: Thu Apr 23, 2020 9:23 pm


Return to Calc

Who is online

Users browsing this forum: Bidouille and 22 guests