If (either of these) return this
 

Hello everyone.

I have in C2 a date that is pulled from another cell

C2 = March 1, 2005

I have a formula that needs to be modified to include either:

=IF(MONTH(C2)=3,"04101",IF(MONTH(C2)=4,"04102",IF( MONTH(C2)=5,"04100",0)))

I would type out all of the months but we are only allowed 7 nested funcitons.

What I need is:
if the month is either 1,4,7, 10 return 04101
if the month is either 2,5,8,11 return 04102
if the month is either 3,6,9,12 return 04100
otherwise leave blank.

How do I say this in the formula?

I tried using the commas to separate the different months but the message
came up I had an something wrong.

=IF(MONTH(C2)=1,4,7,10,"04101",IF(MONTH(C2)=2,5,8, 11,"04102",IF(MONTH(C2)=3,6,9,12,"04100",0)))

Thanks for your help.

=IF(OR(MONTH(C2)={1,4,7,10}),"04101",IF(OR(MONTH(C 2)={2,5,8,11}),"...
you get the idea....

"taxmom" wrote in message
...
Hello everyone.

I have in C2 a date that is pulled from another cell

C2 = March 1, 2005

I have a formula that needs to be modified to include either:

=IF(MONTH(C2)=3,"04101",IF(MONTH(C2)=4,"04102",IF( MONTH(C2)=5,"04100",0)))

I would type out all of the months but we are only allowed 7 nested
funcitons.

What I need is:
if the month is either 1,4,7, 10 return 04101
if the month is either 2,5,8,11 return 04102
if the month is either 3,6,9,12 return 04100
otherwise leave blank.

How do I say this in the formula?

I tried using the commas to separate the different months but the message
came up I had an something wrong.

=IF(MONTH(C2)=1,4,7,10,"04101",IF(MONTH(C2)=2,5,8, 11,"04102",IF(MONTH(C2)=3,6,9,12,"04100",0)))

Thanks for your help.

Maybe something like this would do the trick

=LOOKUP(MONTH(C2),{1,2,3,4,5,6,7,8,9,10,11,12},{"0 4101","04102","04100","04101","04102","04100","041 01","04102","04100","04101","04102","04100"})

Duke

"taxmom" wrote:

Hello everyone.

I have in C2 a date that is pulled from another cell

C2 = March 1, 2005

I have a formula that needs to be modified to include either:

=IF(MONTH(C2)=3,"04101",IF(MONTH(C2)=4,"04102",IF( MONTH(C2)=5,"04100",0)))

I would type out all of the months but we are only allowed 7 nested funcitons.

What I need is:
if the month is either 1,4,7, 10 return 04101
if the month is either 2,5,8,11 return 04102
if the month is either 3,6,9,12 return 04100
otherwise leave blank.

How do I say this in the formula?

I tried using the commas to separate the different months but the message
came up I had an something wrong.

=IF(MONTH(C2)=1,4,7,10,"04101",IF(MONTH(C2)=2,5,8, 11,"04102",IF(MONTH(C2)=3,6,9,12,"04100",0)))

Thanks for your help.

try
if(or(month =1,month=4,month=7,month=10),"04101",if(or(month
=2,month=5,month=8,month=11),"04102",if(month=0,"" ,"04103")))



"taxmom" wrote:

Hello everyone.

I have in C2 a date that is pulled from another cell

C2 = March 1, 2005

I have a formula that needs to be modified to include either:

=IF(MONTH(C2)=3,"04101",IF(MONTH(C2)=4,"04102",IF( MONTH(C2)=5,"04100",0)))

I would type out all of the months but we are only allowed 7 nested funcitons.

What I need is:
if the month is either 1,4,7, 10 return 04101
if the month is either 2,5,8,11 return 04102
if the month is either 3,6,9,12 return 04100
otherwise leave blank.

How do I say this in the formula?

I tried using the commas to separate the different months but the message
came up I had an something wrong.

=IF(MONTH(C2)=1,4,7,10,"04101",IF(MONTH(C2)=2,5,8, 11,"04102",IF(MONTH(C2)=3,6,9,12,"04100",0)))

Thanks for your help.

another way to do it would be
=if( Month(c2)="","","0410" & mod(month(c2),3))
assuming you want text and the alternate for c2 is a space
if it could be text and a numeric output
=if(isnumber(month(c2)),4100+mod(month(c2),3))
and format the cell appropriately to get the leading zero.

"taxmom" wrote:

Hello everyone.

I have in C2 a date that is pulled from another cell

C2 = March 1, 2005

I have a formula that needs to be modified to include either:

=IF(MONTH(C2)=3,"04101",IF(MONTH(C2)=4,"04102",IF( MONTH(C2)=5,"04100",0)))

I would type out all of the months but we are only allowed 7 nested funcitons.

What I need is:
if the month is either 1,4,7, 10 return 04101
if the month is either 2,5,8,11 return 04102
if the month is either 3,6,9,12 return 04100
otherwise leave blank.

How do I say this in the formula?

I tried using the commas to separate the different months but the message
came up I had an something wrong.

=IF(MONTH(C2)=1,4,7,10,"04101",IF(MONTH(C2)=2,5,8, 11,"04102",IF(MONTH(C2)=3,6,9,12,"04100",0)))

Thanks for your help.

And a much shorter version:

=LOOKUP(MOD(MONTH(C1),3),{0,1,2},{"04100","04101", "04102"})

"Duke Carey" wrote:

Maybe something like this would do the trick

=LOOKUP(MONTH(C2),{1,2,3,4,5,6,7,8,9,10,11,12},{"0 4101","04102","04100","04101","04102","04100","041 01","04102","04100","04101","04102","04100"})

Duke

"taxmom" wrote:

Hello everyone.

I have in C2 a date that is pulled from another cell

C2 = March 1, 2005

I have a formula that needs to be modified to include either:

=IF(MONTH(C2)=3,"04101",IF(MONTH(C2)=4,"04102",IF( MONTH(C2)=5,"04100",0)))

I would type out all of the months but we are only allowed 7 nested funcitons.

What I need is:
if the month is either 1,4,7, 10 return 04101
if the month is either 2,5,8,11 return 04102
if the month is either 3,6,9,12 return 04100
otherwise leave blank.

How do I say this in the formula?

I tried using the commas to separate the different months but the message
came up I had an something wrong.

=IF(MONTH(C2)=1,4,7,10,"04101",IF(MONTH(C2)=2,5,8, 11,"04102",IF(MONTH(C2)=3,6,9,12,"04100",0)))

Thanks for your help.

Fantastic!!!!!

Thank you all so much.


"N Harkawat" wrote:

=IF(OR(MONTH(C2)={1,4,7,10}),"04101",IF(OR(MONTH(C 2)={2,5,8,11}),"...
you get the idea....

"taxmom" wrote in message
...
Hello everyone.

I have in C2 a date that is pulled from another cell

C2 = March 1, 2005

I have a formula that needs to be modified to include either:

=IF(MONTH(C2)=3,"04101",IF(MONTH(C2)=4,"04102",IF( MONTH(C2)=5,"04100",0)))

I would type out all of the months but we are only allowed 7 nested
funcitons.

What I need is:
if the month is either 1,4,7, 10 return 04101
if the month is either 2,5,8,11 return 04102
if the month is either 3,6,9,12 return 04100
otherwise leave blank.

How do I say this in the formula?

I tried using the commas to separate the different months but the message
came up I had an something wrong.

=IF(MONTH(C2)=1,4,7,10,"04101",IF(MONTH(C2)=2,5,8, 11,"04102",IF(MONTH(C2)=3,6,9,12,"04100",0)))

Thanks for your help.