Page 1 of 1
Display date if conditions are true
Posted: Wed Dec 12, 2018 7:07 am
by Joeywc
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
Re: Display date if conditions are true
Posted: Wed Dec 12, 2018 7:52 am
by Zizi64
Please upload your ODF type sample file here.
Re: Display date if conditions are true
Posted: Wed Dec 12, 2018 11:51 am
by Joeywc
I want to be able for the wk start to be every 8 days and display only if colum a has supporting dates
Re: Display date if conditions are true
Posted: Wed Dec 12, 2018 12:00 pm
by mikele
Hi,
Code: Select all
=IF(COUNTIF($A$2:$A$100;">="&(H9+8));H9+8;"")
Re: Display date if conditions are true
Posted: Wed Dec 12, 2018 9:45 pm
by Joeywc
That gives me a value of 1/7/00
Re: Display date if conditions are true
Posted: Wed Dec 12, 2018 10:08 pm
by mikele
Hello,
me not ...
Re: Display date if conditions are true
Posted: Wed Dec 12, 2018 10:28 pm
by Zizi64
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.
Re: Display date if conditions are true
Posted: Thu Dec 13, 2018 6:44 pm
by Joeywc
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?
Re: Display date if conditions are true
Posted: Thu Dec 13, 2018 8:09 pm
by Zizi64
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
Re: Display date if conditions are true
Posted: Fri Dec 14, 2018 5:17 am
by Joeywc
Your_condition? Would that be the part? Or start with if(countif?
Re: Display date if conditions are true
Posted: Fri Dec 14, 2018 5:22 am
by Joeywc
=IF(ISBLANK(H9;"";IF(COUNTIF( $A$2:$A$100;">="&(H9+8));H9+8;"")).... gives err508
Re: Display date if conditions are true
Posted: Fri Dec 14, 2018 8:17 am
by robleyd
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.
Re: Display date if conditions are true
Posted: Fri Dec 14, 2018 8:39 am
by Zizi64
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.
Re: Display date if conditions are true
Posted: Sat Dec 15, 2018 5:57 am
by Joeywc
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
Re: Display date if conditions are true
Posted: Sat Dec 15, 2018 9:46 am
by Zizi64
Please upload your actual version of your ODF type sample file here.
Re: Display date if conditions are true
Posted: Sat Dec 15, 2018 10:16 am
by robleyd
Re: Display date if conditions are true
Posted: Sun Dec 16, 2018 8:38 am
by Joeywc
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
Re: Display date if conditions are true
Posted: Sun Dec 16, 2018 4:05 pm
by RusselB
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.
Re: Display date if conditions are true
Posted: Sun Dec 16, 2018 11:02 pm
by Joeywc
Correct. That's my issue. I would like the others be blank if the one before is blank
Re: Display date if conditions are true
Posted: Sun Dec 16, 2018 11:45 pm
by Villeroy
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.
Re: Display date if conditions are true
Posted: Mon Dec 17, 2018 12:17 am
by Joeywc
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
Re: Display date if conditions are true
Posted: Mon Dec 17, 2018 12:23 am
by RusselB
Try
instead of the code you're currently using
Re: Display date if conditions are true
Posted: Mon Dec 17, 2018 8:34 am
by Zizi64
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.