I am trying to create a formulae that makes a cell say "Sale Open" or "Sale Closed". It should say "Sale Open" only during May. And "Sale Closed" any other month of the year so far I have:

=IF(J13>=DATEVALUE("1/5/2010"),Sale Open,"Sale Closed")

In cell J13 has the formulae=Today()

Please Help!

Try this: =IF(MONTH(TODAY())=5,"Sale Open","Sale Closed")

Just insert the formula into any cell, and it should show "Sale Closed", since the current date is 4/26/2010.

MIKE

Hi, This should work for May of any year:

=IF(MONTH(J13)= 5,"Sale Open","Sale Closed")You can include Today inside this formula:

=IF(MONTH(TODAY())= 5,"Sale Open","Sale Closed")Note that both messages must be in double quotes.

Regards

Please keep this in mind if you choose to go with Humar's formula that contains the TODAY() function: The TODAY() function is consider "volatile" by Excel. What this means is that every time you open a file that contains the TODAY() function, it will force a calculation in that cell and Excel will consider the file as being "changed".

When you try to close the file, Excel will ask you if you want to save the changes, even if you made no "manual" changes to it. This can be a bit of a pain.

I recently thought that the TODAY() function would make a few of my spreadsheets easier to use, when it fact it got to be extremely inconvenient.

Sometimes I open these spreadsheets just to look at some data and other times I right-click the file name and choose Print. In both of these cases, Excel asks me if I wanted to "Save the Changes" even though I haven't done anything.

I also found that it was confusing my clients because they didn't know why they were being asked to save the changes after just looking at the contents of the files.

In response to DerbyDad03's comment about the formula containing TODAY(): It should be noted that your spreadsheet already contains the TODAY() function in cell J13, so including it in the Open/Closed formula makes no difference.

Your Workbook is still recalculated by the 'volatile' Today() function.

TODAY() is one of a small group of functions that recalculate when a workbook is opened as well as when anything else causes the workbook to recalculate.

Regards

Thanks guys!

You are correct, Humar. I missed the fact that J13 that was mentioned in OP.

Ask Your Question

Weekly Poll

How long do you think until flying cars or taxis are common?

Discuss in The Lounge

Poll History