Date IF statement help

Microsoft Excel 2003 (full product)
April 27, 2010 at 05:59:40
Specs: Windows XP
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


Please Help!

See More: Date IF statement help

Report •

April 27, 2010 at 06:30:48
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.


Report •

April 27, 2010 at 06:32:04

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.


Report •

April 27, 2010 at 07:01:18
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.

Report •

Related Solutions

April 27, 2010 at 07:08:37
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.


Report •

April 27, 2010 at 07:09:12
Thanks guys!

Report •

April 27, 2010 at 08:07:44
You are correct, Humar.

I missed the fact that J13 that was mentioned in OP.

Report •

Ask Question