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. My dates are formatted as a date in M/D/YYYY format. It works fine without the "OR" statement (so it's not flagging empty cells). If the cell is blank without the OR, then I get #VALUE error (that one I understand). It seems that adding the blank cell conditional is messing things up. Any help is appreciated!

My boss spotted my mistake. I needed to specify $A35 again, so it should have read:

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

"KeriM" wrote:
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.

Errata.... I wrote:
=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.