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?


--
Wzbell
------------------------------------------------------------------------
Wzbell's Profile: http://www.excelforum.com/member.php...o&userid=37890
View this thread: http://www.excelforum.com/showthread...hreadid=574291

That formula didn't work for me (it is plain wrong), and I can't see why the
OR, but this worked

=IF(AND(MONTH(TODAY())=MONTH(G5)+4,YEAR(TODAY())=Y EAR(G5)),"yes","")

--
HTH

Bob Phillips

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

"Wzbell" wrote in
message ...

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?


--
Wzbell
------------------------------------------------------------------------
Wzbell's Profile:
http://www.excelforum.com/member.php...o&userid=37890
View this thread: http://www.excelforum.com/showthread...hreadid=574291

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())=(MON TH(U5)+4),MONTH(TODAY())=(MONTH(V5)+4),MONTH(TODAY ())=(MONTH(W5)+4),MONTH(TODAY())=(MONTH(Z5)+4),MON TH(TODAY())=(MONTH(AA5)+4),MONTH(TODAY())=(MONTH(A D5)+4),MONTH(TODAY())=(MONTH(AE5)+4),MONTH(TODAY() )=(MONTH(AF5)+4),MONTH(TODAY())=(MONTH(AI5)+4),MON TH(TODAY())=(MONTH(AJ5)+4),MONTH(TODAY())=(MONTH(A M5)+4),MONTH(TODAY())=(MONTH(AN5)+4),MONTH(TODAY() )=(MONTH(AO5)+4),MONTH(TODAY())=(MONTH(AR5)+4),MON TH(TODAY())=(MONTH(AS5)+4),MONTH(TODAY())=(MONTH(A V5)+4),MONTH(TODAY())=(MONTH(AW5)+4),MONTH(TODAY() )=(MONTH(AX5)+4),MONTH(TODAY())=(MONTH(BA5)+4),MON TH(TODAY())=(MONTH(BB5)+4),MONTH(TODAY())=(MONTH(B E5)+4),MONTH(TODAY())=(MONTH(BF5)+4)),"yes","")


--
Wzbell
------------------------------------------------------------------------
Wzbell's Profile: http://www.excelforum.com/member.php...o&userid=37890
View this thread: http://www.excelforum.com/showthread...hreadid=574291

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","")


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=574291

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


--
Wzbell
------------------------------------------------------------------------
Wzbell's Profile: http://www.excelforum.com/member.php...o&userid=37890
View this thread: http://www.excelforum.com/showthread...hreadid=574291

=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,5 1},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")=T EXT(TODAY(),"yymm")))

--
HTH

Bob Phillips

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

"Wzbell" wrote in
message ...

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),MON TH(TODAY())=(MONTH(Q5)+4),
MONTH(TODAY())=(MONTH(R5)+4),MONTH(TODAY())=(MONTH (U5)+4),MONTH(TODAY())=(MO
NTH(V5)+4),MONTH(TODAY())=(MONTH(W5)+4),MONTH(TODA Y())=(MONTH(Z5)+4),MONTH(T
ODAY())=(MONTH(AA5)+4),MONTH(TODAY())=(MONTH(AD5)+ 4),MONTH(TODAY())=(MONTH(A
E5)+4),MONTH(TODAY())=(MONTH(AF5)+4),MONTH(TODAY() )=(MONTH(AI5)+4),MONTH(TOD
AY())=(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),M ONTH(TODAY())=(MONTH(AW5)+
4),MONTH(TODAY())=(MONTH(AX5)+4),MONTH(TODAY())=(M ONTH(BA5)+4),MONTH(TODAY()
)=(MONTH(BB5)+4),MONTH(TODAY())=(MONTH(BE5)+4),MON TH(TODAY())=(MONTH(BF5)+4)
),"yes","")


--
Wzbell
------------------------------------------------------------------------
Wzbell's Profile:
http://www.excelforum.com/member.php...o&userid=37890
View this thread: http://www.excelforum.com/showthread...hreadid=574291

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


--
Wzbell
------------------------------------------------------------------------
Wzbell's Profile: http://www.excelforum.com/member.php...o&userid=37890
View this thread: http://www.excelforum.com/showthread...hreadid=574291

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

--
HTH

Bob Phillips

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

"Wzbell" wrote in
message ...

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


--
Wzbell
------------------------------------------------------------------------
Wzbell's Profile:
http://www.excelforum.com/member.php...o&userid=37890
View this thread: http://www.excelforum.com/showthread...hreadid=574291