If date is in the month of Sept, then "1", otherwise "2"

[Guest]

cell a1 = the date

a1 could equal 9/15/2007, 8/20/2007, 9/26/2007, 5/30/2006, etc.

formula please?:

if the date in cell a1 falls in the month of september, then "1", otherwise
"2"

thanks very much!

 

[Ron Coderre]

Try this
=1+(MONTH(A1)<>9)

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Guest]

That does help, thanks.

I've modified it to this: =IF(MONTH(N10)=A5,N20,M20)

So if the month in cell n10 equals today's month (in cell a5), then use the
data in cell n20, otherwise use next month's data which is in cell m20.

I will probably have a bunch of nested Ifs where M20 is, becuase I have to
reference a few date ranges.

Thanks very much Ron!

 

[Guest]

I'm glad I could point you in the right direction....thanks for the feedback.

***********
Regards,
Ron

XL2003, WinXP

 

[Guest]

for some reason the month function doesn't seem to work between links of
separate worksheets.

I changed the formula to this:

=IF(EOMONTH(Monthly!N10,0)=EOMONTH(A5,0),Monthly!N20,IF(EOMONTH(Monthly!M10,0)=EOMONTH(A5,0),Monthly!M20,"xxx"))

and it works now, but if you know of a better way, I'd like to hear.

Thanks again.

 

[Guest]

The MONTH function doesn't care which sheet it's referring to.

Anyway, perhaps one of these variations:

=IF(MONTH(Monthly!N10)=MONTH(Sheet1!A5),Monthly!N20,IF(MONTH(Monthly!M10)=MONTH(Sheet1!A5),Monthly!M20,"xxx"))

or

=CHOOSE(SUMPRODUCT((MONTH(Monthly!M10:N10)=MONTH(A5))*{1,2})+1,"xxx",Monthly!M20,Monthly!N20)

or...

=CHOOSE(SUMPRODUCT((TEXT(Monthly!M10:N10,"yyyymm")=TEXT(A5,"yyyymm"))*{1,2})+1,"xxx",Monthly!M20,Monthly!N20)

Note: in case text wrap impacts the display, there are NO spaces in those
formulas.

Does that help?
***********
Regards,
Ron

XL2003, WinXP