Forum Discussion
Create a cell IF formula based on Month
I need to create a cell in Excel that will work a formula during a certain month and will display a numeric value when it is not that particular month (it is for a daily expenditure value that automatically decrements daily for that particular month).
I have the formula to change the value of the cell - {=((30)-DAY(TODAY()))*100} and it works fine. I want to write an IF formula that will only use that formula for the month of that budget tab.
Here is the last formula I have tried (without success) - =IF(MONTH(8), then_value,=((30)-DAY(TODAY()))*100, otherwise_value, "3000.00")
In summary, I want the budget tab for August to count down based on the day of the month, but I want every other tabs (the other 11 months) to display a straight numeric value (preferably "0.00" for January - July and "3000.00 for September - December)
I've exhausted everything I can think of to try. If you can help me I would greatly appreciate it.
Thank you.
Ed
7 Replies
I think this works for you..
=IF(MONTH(TODAY())=8,30-DAY(TODAY())*100, IF(MONTH(TODAY()) < 8,0,3000))
MONTH expects a date to check, you cant say if month(8) you have to say if month(a date)=8
This formula is checking things like this: if todays month = 8, perform your 30-today*100. Else if todays month is less than 8 the result is 0, else the result is 3000.
I tried your formula and it seemed to work with the exception of the math formula. For some reason 30-DAY(TODAY())*100 returns a value of -1670. I can't figure that one out. I changed the "8" to "9" in your formula to correlate with September and I received that value. I put only the above formula in with the same result.
Any thoughts?
Thank you for your help.
Edward, and what do you expect to be returned?
Today is Sep 17th. DAY() of it =17. Multiply on 100 = 1700. 30 minus that gives -1670.
