IF(OR Function with years

W

Wzbell

Please provide any help you can...

I am working on a spreadsheet that identifies due dates including year
and reports back yes if the criteria is met. I have a formula that
works but I can't figure out how to add the year in. Here's what I
have so far for one cell (multiplied several times for a range of cells
in actual spreadsheet):

IF(OR(MONTH(TODAY())=(MONTH(G5)+4)"yes","")

This works ok but cannot not distinguish between years. What should I
add?
 
W

Wzbell

Here is the full function. As you can see it covers several cells.
Will you formula work if I use it for this? and of cousre multiply it
for the numerous cells.

=IF(OR(MONTH(TODAY())=(MONTH(G5)+4),MONTH(TODAY())=(MONTH(H5)+4),MONTH(TODAY())=(MONTH(K5)+4),MONTH(TODAY())=(MONTH(N5)+4),MONTH(TODAY())=(MONTH(Q5)+4),MONTH(TODAY())=(MONTH(R5)+4),MONTH(TODAY())=(MONTH(U5)+4),MONTH(TODAY())=(MONTH(V5)+4),MONTH(TODAY())=(MONTH(W5)+4),MONTH(TODAY())=(MONTH(Z5)+4),MONTH(TODAY())=(MONTH(AA5)+4),MONTH(TODAY())=(MONTH(AD5)+4),MONTH(TODAY())=(MONTH(AE5)+4),MONTH(TODAY())=(MONTH(AF5)+4),MONTH(TODAY())=(MONTH(AI5)+4),MONTH(TODAY())=(MONTH(AJ5)+4),MONTH(TODAY())=(MONTH(AM5)+4),MONTH(TODAY())=(MONTH(AN5)+4),MONTH(TODAY())=(MONTH(AO5)+4),MONTH(TODAY())=(MONTH(AR5)+4),MONTH(TODAY())=(MONTH(AS5)+4),MONTH(TODAY())=(MONTH(AV5)+4),MONTH(TODAY())=(MONTH(AW5)+4),MONTH(TODAY())=(MONTH(AX5)+4),MONTH(TODAY())=(MONTH(BA5)+4),MONTH(TODAY())=(MONTH(BB5)+4),MONTH(TODAY())=(MONTH(BE5)+4),MONTH(TODAY())=(MONTH(BF5)+4)),"yes","")
 
D

daddylonglegs

Do you mean that during August 2006 you want the formula to return "Yes
for any date in April 2006? In which case

=IF(DATEDIF(G5,TODAY()-DAY(TODAY()),"m")=3,"Yes",""
 
W

Wzbell

That might work the only problem I ran into as that excel says its too
long when I enter 29 cell addresses to the formula. Any other
suggestions? But yes, daddylonglegs.. that's what I'm looking for
 
B

Bob Phillips

=SUMPRODUCT(--(TEXT(DATE(YEAR(N(OFFSET(G5:Z5,0,{0,1,4,7,10,11,14,15,16,19,20
,23,24,25,28,29,32,33,34,37,38,41,42,43,46,47,50,51},1,1))),
MONTH(N(OFFSET(G5:Z5,0,{0,1,4,7,10,11,14,15,16,19,20,23,24,25,28,29,32,33,34
,37,38,41,42,43,46,47,50,51},1,1)))+4,1),"yymm")=TEXT(TODAY(),"yymm")))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
W

Wzbell

OK I tried the last formula by Bob Phillips and get a 1/0/00 response.
Any clue of what's going on
 
B

Bob Phillips

Format it as general, it is defaulting to a date.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Top