# Update Months in Excel formula

August 23, 2010 at 13:22:44
Specs: Windows XP
 =IF(A3=DATEVALUE("9/1/2010")=AND(M3=DATEVALUE("8/30/2010"))=AND(L3=("S")),N3,0)i am using the above and want to know if there is a way for the dates to advance monthly when copying to a range in the column?

See More: Update Months in Excel formula

#1
August 23, 2010 at 15:06:03
 A posting tip:When posting in a Help forum such as this, please try to use a Subject Line that is relevant to the content of your post.If everyone used a generic subject line such as Excel Formula, we wouldn't be able to tell one question from another. It would also make searching the Archives much more difficult.I have edited the Subject Line for this thread.DerbyDad03Office Forum Moderator

Report •

#2
August 23, 2010 at 15:13:56
 I'm not sure what you are trying to accomplish with this formula.You used the AND function twice, yet in both cases you are only checking one condition.Typically, an AND function in an IF statement would be used like this:=IF(AND(A1=5, B1=4), value_if_true, value_if_false)AND(A1=5, B1=4) would only be TRUE is both conditions were TRUE.AND(M3=DATEVALUE("8/30/2010")) doesn't make sense since it it not checking for 2 (or more) conditions to be TRUE.What exactly are you trying to do with your formula?

Report •

#3
August 23, 2010 at 16:33:55
 thanks for the tip and the response. column A has date(the first of each month), column B is a payment, C is interest, D is Balance. this formula resides in column B. column L is the name of the account, column M has date(the 30th of each month) column N is where the payment amount is entered. A-D is the account information, L-N is the corresponding payment schedule. the dollar amounts will vary. I want B to take the amount from N but only if the criteria are met. If the formula is copied down column B is there someway the date would advance as well as the cell reference? Thanks. (I know I am way over thinking this.)

Report •

Related Solutions

#4
August 23, 2010 at 17:07:26
 re: I want B to take the amount from N but only if the criteria are met.What criteria?You said that "column A has date (the first of each month)" and column M has date (the 30th of each month)" so why would you need to check and see if they contain a DATEVALUE for the 1st and 30th of each month?Am I missing something?

Report •

#5
August 24, 2010 at 04:37:01
 Hi,I am guessing that you want to cell B3 to show either the value in N3 if all three conditions are met:A3 contains the date 31 September 2010M3 contains the date 31 August 2010, andL3 contains the letter "S"This formula will do that:=IF(AND(A3=DATEVALUE("01/Sep/2010"),M3=DATEVALUE("31/Aug/2010"),L3="S"),N3,0)To get the dates in the formula to increment by one month on each successive row, try this:1. The following uses the EOMONTH() function. This is part of Excel's "Analysis Toolpak" which is not active by default in Excel 2003 and earlier. In Excel 2007 and later EOMONTH is part of the standard installed functions.If you have Excel 2003 or earlier, click 'Tools' on the menu bar and select 'Add-Ins...'From the list of Add-Ins check the box beside 'Analysis Toolpak' and click OK.2. make the following entries on your worksheet.In cell X1 enter 01/Aug/2010 (If X1 is in use - use another cell and substitute that cell for X1 in the following formula (but make sure to use the \$ signs, so that the reference to that cell does not change as you drag the formula).In B3 enter this formula:=IF(AND(A3=EOMONTH(\$X\$1,ROW()-3)+1,M3=EOMONTH(\$X\$1,ROW()-3),L3="S"),N3,0)This formula tests A3 for 1 day after the last day of August 2010, i.e., 01 September 2010and it tests M3 for the last day of August 2010Drag the formula down one row and the the formula will now test A4 for 01 October 2010and M4 for 30 September 2010.The formula works because the ROW() function returns the number of the row it is on, so in cell B3, ROW() returns 3. By subtracting 3 from it the End of Month function finds the end of the month zero months ahead of the date in X1, i.e., this month. On the next row, ROW() is 4 and 4-3 =1, so it returns the end of the month One month ahead of the date in X1.Also note that entering dates as 9/1/2010 may be interpreted by Excel as 09 January, and not 01 September. It is better to be absolutely clear and use month abbreviations such as "Sep"Hope this is of some use.Regards

Report •

#6
August 24, 2010 at 10:34:27
 Humar,The OP says that column M has date (the 30th of each month).The 30th is not the last day August 2010, nor is it the last day of many other months. Unless that OP changes his story, EOMONTH might not work for Column M.I'm still waiting for an answer to my question. If Column A and Column M already contain specific dates as the OP implies, then why do we need to check for them in an IF(AND(...)) statement?Are those dates already there or are they added/modified by the user?

Report •

#7
August 24, 2010 at 15:48:12
 thanks that worked perfectly.

Report •

#8
August 25, 2010 at 05:06:03
 DerbyDad03,I guess my guess was right !RegardsHumar

Report •

#9
August 25, 2010 at 06:38:32
 I guess so!

Report •