Display date if conditions are true

Discuss the spreadsheet application

Display date if conditions are true

Postby Joeywc » Wed Dec 12, 2018 7:07 am

Hello,
In colum a I have dates. In colum H I have summarized data in table. I want to keep from having to enter data everytime.
Ie... h9 has a value of a date, I want h10 h11 and so on to display previous h+8 only if colum a has dates according.....
H9 is 12/7/18.
H10 needs to be 12/15/18 only if a2:a100 has a date on or after h9+8.
I tried =if(a2:a100>=h9+8;h9+8;"") but getting blank results
Openoffice 4.1.5 on windows 10
Joeywc
 
Posts: 10
Joined: Wed Dec 12, 2018 6:48 am

Re: Display date if conditions are true

Postby Zizi64 » Wed Dec 12, 2018 7:52 am

Please upload your ODF type sample file here.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
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: 8314
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Display date if conditions are true

Postby Joeywc » Wed Dec 12, 2018 11:51 am

I want to be able for the wk start to be every 8 days and display only if colum a has supporting dates
Attachments
sample .ods
(7.92 KiB) Downloaded 24 times
Openoffice 4.1.5 on windows 10
Joeywc
 
Posts: 10
Joined: Wed Dec 12, 2018 6:48 am

Re: Display date if conditions are true

Postby mikele » Wed Dec 12, 2018 12:00 pm

Hi,
Code: Select all   Expand viewCollapse view
=IF(COUNTIF($A$2:$A$100;">="&(H9+8));H9+8;"")
LibreOffice 5.4.7 on LinuxMint/WinXP/Win7
mikele
 
Posts: 45
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: Display date if conditions are true

Postby Joeywc » Wed Dec 12, 2018 9:45 pm

That gives me a value of 1/7/00
Openoffice 4.1.5 on windows 10
Joeywc
 
Posts: 10
Joined: Wed Dec 12, 2018 6:48 am

Re: Display date if conditions are true

Postby mikele » Wed Dec 12, 2018 10:08 pm

Hello,
me not ...
Attachments
sample .ods
(10.03 KiB) Downloaded 18 times
LibreOffice 5.4.7 on LinuxMint/WinXP/Win7
mikele
 
Posts: 45
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: Display date if conditions are true

Postby Zizi64 » Wed Dec 12, 2018 10:28 pm

That gives me a value of 1/7/00

Then the H9 has a zero value in YOUR file. For example it contains a textual data with zero value.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
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: 8314
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Display date if conditions are true

Postby Joeywc » Thu Dec 13, 2018 6:44 pm

Got it to work when h9 has date... but when fill down if h10 is blank then h11 thru h61fills with bad data... so on and so forth... shouldn't the others be blank? Yes It uses same formula except h9 become h10+8, h11+8... and so on. If h10 is blank then so should h11.... and so on right?
Openoffice 4.1.5 on windows 10
Joeywc
 
Posts: 10
Joined: Wed Dec 12, 2018 6:48 am

Re: Display date if conditions are true

Postby Zizi64 » Thu Dec 13, 2018 8:09 pm

Got it to work when h9 has date... but when fill down if h10 is blank then h11 thru h61fills with bad data...


Try the
=IF(ISBLANK(H9;"";your_condition))
formula
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
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: 8314
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Display date if conditions are true

Postby Joeywc » Fri Dec 14, 2018 5:17 am

Your_condition? Would that be the part? Or start with if(countif?
Openoffice 4.1.5 on windows 10
Joeywc
 
Posts: 10
Joined: Wed Dec 12, 2018 6:48 am

Re: Display date if conditions are true

Postby Joeywc » Fri Dec 14, 2018 5:22 am

=IF(ISBLANK(H9;"";IF(COUNTIF( $A$2:$A$100;">="&(H9+8));H9+8;"")).... gives err508
Openoffice 4.1.5 on windows 10
Joeywc
 
Posts: 10
Joined: Wed Dec 12, 2018 6:48 am

Re: Display date if conditions are true

Postby robleyd » Fri Dec 14, 2018 8:17 am

508
Error: Pair missing
Missing bracket, for example, closing brackets, but no opening brackets

Count your brackets. Use your F1- Help to find the meaning of the error messages.
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2982
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Display date if conditions are true

Postby Zizi64 » Fri Dec 14, 2018 8:39 am

You need three closeing brackets at the end of the formula. One for the IF(), ant another for ISBLANK() and the third for your condition.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
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: 8314
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Display date if conditions are true

Postby Joeywc » Sat Dec 15, 2018 5:57 am

Code works for h10 but same code(replacing h9 each line) still shows bad data? I dont know why it works for one but not the rest( h11,h12... h62)... 52 weeks starting with h9
Openoffice 4.1.5 on windows 10
Joeywc
 
Posts: 10
Joined: Wed Dec 12, 2018 6:48 am

Re: Display date if conditions are true

Postby Zizi64 » Sat Dec 15, 2018 9:46 am

Please upload your actual version of your ODF type sample file here.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
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: 8314
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Display date if conditions are true

Postby robleyd » Sat Dec 15, 2018 10:16 am

If you are new to spreadsheets, you may find the following to be useful resources.

[Tutorial] Ten concepts that every Calc user should know

OpenOffice Spreadsheet Tutorial for Beginners with Examples

And I suspect directly related to your question - [Tutorial] Absolute, relative and mixed references
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2982
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Display date if conditions are true

Postby Joeywc » Sun Dec 16, 2018 8:38 am

Zizi64 wrote:Please upload your actual version of your ODF type sample file here.

see the dates in h11,h12,h13 are off but h10 is right
Attachments
SAMPLE.ods
(57.51 KiB) Downloaded 18 times
Openoffice 4.1.5 on windows 10
Joeywc
 
Posts: 10
Joined: Wed Dec 12, 2018 6:48 am

Re: Display date if conditions are true

Postby RusselB » Sun Dec 16, 2018 4:05 pm

H11 is off due to the fact that the formula for calculating the date refers to H10, which is blank, thus returning the equivalent of a 0 value for other calculations.
H12 and H13 have similar problems, but they can all be traced back to the fact that you have a blank, thus a 0 value, in H10.
OpenOffice 4.1.7 and LibreOffice 6.0.6.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: 5483
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Display date if conditions are true

Postby Joeywc » Sun Dec 16, 2018 11:02 pm

Correct. That's my issue. I would like the others be blank if the one before is blank
Openoffice 4.1.5 on windows 10
Joeywc
 
Posts: 10
Joined: Wed Dec 12, 2018 6:48 am

Re: Display date if conditions are true

Postby Villeroy » Sun Dec 16, 2018 11:45 pm

Joeywc wrote:Correct. That's my issue. I would like the others be blank if the one before is blank

This implies a loss of information.
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.x
User avatar
Villeroy
Volunteer
 
Posts: 27200
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Display date if conditions are true

Postby Joeywc » Mon Dec 17, 2018 12:17 am

I thought that's the reason for the if,countif code. It only supposed to calculate if there's value in row above. Otherwise leave blank
Openoffice 4.1.5 on windows 10
Joeywc
 
Posts: 10
Joined: Wed Dec 12, 2018 6:48 am

Re: Display date if conditions are true

Postby RusselB » Mon Dec 17, 2018 12:23 am

Try
Code: Select all   Expand viewCollapse view
=if(isnum(H9);H9+8;"")

instead of the code you're currently using
OpenOffice 4.1.7 and LibreOffice 6.0.6.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: 5483
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Display date if conditions are true

Postby Zizi64 » Mon Dec 17, 2018 8:34 am

I thought that's the reason for the if,countif code. It only supposed to calculate if there's value in row above. Otherwise leave blank


There are many functions to examine the content of a cell:

ISBLANK()
ISNUMBER()
ISNA()
ISERROR()
ISERR()
ISEVEN()
ISODD()
...and more.

Read the HELP, and use the function what you need for your task.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
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: 8314
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary


Return to Calc

Who is online

Users browsing this forum: No registered users and 24 guests