IF(OR Function with years

[Wzbell]

Please provide any help you can...

I am working on a spreadsheet that identifies due dates (4 months past) 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?

[Bob Phillips]

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())=YEAR(G5)),"yes","")

--
HTH

Bob Phillips

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

"Wzbell" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

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

[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

[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)

"Wzbell" <[email protected]> wrote in
message news:[email protected]...
>
> 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())=(MO
NTH(V5)+4),MONTH(TODAY())=(MONTH(W5)+4),MONTH(TODAY())=(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),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","")
>
>
> --
> Wzbell
> ------------------------------------------------------------------------
> Wzbell's Profile:
http://www.excelforum.com/member.php...o&userid=37890
> View this thread: http://www.excelforum.com/showthread...hreadid=574291
>

[Wzbell]

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

[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)

"Wzbell" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Wzbell]

It worked great thanks for your help. If I want it to report dates a month from now how would I set that up? As in sum dates that fall next month?

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

--
HTH

Bob Phillips

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

"Wzbell" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>