If formula producing #VALUE Error

[KeriM]

I'm trying to set up a spreadsheet that has a contiguous set of date
that decrease by 7 days each row. I want it to stop at January 1st o
every year (ideally, I'd like to not hardcode the year, but I did i
that way as a test run). I tried this formula:

=IF(OR($A35=DATE(2012,1,1),""),"",$A35-7)

But I'm getting a #VALUE error. My dates are formatted as a date i
M/D/YYYY format. It works fine without the "OR" statement (so it's no
flagging empty cells). If the cell is blank without the OR, then I ge
#VALUE error (that one I understand). It seems that adding the blan
cell conditional is messing things up. Any help is appreciated

 

[joeu2004]

I'm trying to set up a spreadsheet that has a contiguous set of dates
that decrease by 7 days each row. I want it to stop at January 1st of
every year (ideally, I'd like to not hardcode the year, but I did it
that way as a test run). I tried this formula:
=IF(OR($A35=DATE(2012,1,1),""),"",$A35-7)
But I'm getting a #VALUE error.

Perhaps:

=IF(OR($A35="",MONTH($A35)=1),"",$A35-7)

That does assume that =ISNUMBER(A35) et al is TRUE. If not, post an update
and tell us more about the contents of A35 et al.

 

[joeu2004]

=IF(OR($A35="",MONTH($A35)=1),"",$A35-7)

That does result in a #VALUE error if the value of A35 is the null string
(""). One of the following is better:

=IF($A35="","",IF(MONTH($A35)=1,"",$A35-7))

=IF(OR($A35="",MONTH(N($A35))=1),"",$A35-7)

=IF(MONTH(N($A35))=1,"",$A35-7)

In the second formula, the test $A35="" is actually redundant, as
demonstrated by the third formula. But it might be more intuitive. Then
N() function returns zero if $A35 is non-numeric.