MrExcel.com - Your One Stop for Excel Tips & Solutions

Dates within "IF" statements

Posted by Robert Dorr on April 21, 2001 2:29 AM

Hi,
I am having problems with the syntax for the use of dates within "If" statements.

I have twelve columns, with the column headers comprised of a EDATE() function in the format mmm-yy.
I wish to reference these columns with a nested "If" statement to the effect:
=IF(C1=April, ......,if(C1=July,.....,If(C1=October,.....,....))) "C1" in this example is the column header in the EDATE()format described above.

I do not know how to reference the month ("March") in the formula. Everything I try does not work!

Thanks for your previous help!..... Robert Dorr

Posted by Aladin Akyurek on April 21, 2001 2:41 AM

Robert

=IF(MONTH(C1)=3,...)

would test whether C1 contains a date within the month March.

I've the impression that you're attempting to build a formula containing too many IFs. There is a limit to the number of IFs you can have in a formula.

Care to describe what you want to do?

Aladin

==============

Posted by Dave Hawley on April 21, 2001 3:06 AM


Hi Robert

The IF formula can only be nested to a level of eight. To extract the Month from a date you use the MONTH formula.

If you need to test ALL 12 months then I woud suggest using the CHOOSE formula, this is ideal when working with Months as they follow the numeric order 1 to 12, so you could as an example use this:

=CHOOSE(MONTH(C1),"Im Jan","Im Feb","Im Mar","Im Apr","Im may","Im jun","Im Jul","Im Aug","Im Sep","Im Oct","Im Nov","Im Dec")


so if the date in C1 was within March this would return "Im mar".


Dave

OzGrid Business Applications

Posted by Dave Hawley on April 21, 2001 3:08 AM

Oops typo, make that seven not eight for IF.

OzGrid Business Applications

Posted by Robert Dorr on April 25, 2001 4:33 PM

Thanks Aladin & Dave I think you have solved my query ( i will now go and try your various suggestions !).
However in response to "Aladin":

I have a worheet that has 12 columns with consecutive monthly (MMM-yy) date headers at C1 through to N1.
C1 or the starting month can be any month nominated by the user. Within each of the 'month" columns there is a calculation of "Tax" for that month.
Tax is paid quarterly in the months of January, April,July, and October. I wish to reflect the payment of the tax in the month it is due so I wanted to test each of the monthly tax calculations row cells to see if it was a month in which payment was due.If "true", "then" add up the previous 3 monthly (row)cells that contained the tax calculation.

Posted by Aladin Akyurek on April 26, 2001 10:47 AM

Hi Robert

It's ust now that I've seen your post. I must say I'm bad at taxes (not that I don't pay mine). I'd appreciate seeing 5 to 10 rows of your data. If you like to do so, you can activate an empty cell, go the formula bar, type the equal sign, and select a range of 5 to 10 rows (header row included). At this point, hit CONTROL+SHIFT+ENTER. Then select the range to the right of =-sign in the formula bar and hit F9. Cut the expanded array and paste it in your follow-up post.

Aladin