# Thread: IF and Date (Excel 2000)

1. ## IF and Date (Excel 2000)

A colleague has asked if he can have an IF formula that works on dates rather than figures. As I have trouble using date functions in Excel, I thought I would ask the experts. Here is his question

If Cell A1 <= 31/12/02 then return formula A, however, IF Cell A1 > 31/12/02 then return formula B

I don't know what formula A or B is?

2. ## Re: IF and Date (Excel 2000)

If you (or your colleague) put 31 December 2002 in a cell, say B1, your formula becomes

=IF(A1<=B1,<formula A here>,<formula B here>)

If you don't want to put 31 December 2002 in a cell, you can use

=IF(A1<=DATE(2002,12,31),<formula A here>,<formula B here>)

or

=IF(A1<=DATEVALUE("31/12/02"),<formula A here>,<formula B here>)

The latter formula will fail if used on a computer with a different date setting.

3. ## Re: IF and Date (Excel 2000)

Thank you very much from me and thank you from my colleague. I have passed the message on.

4. ## Re: IF and Date (Excel 2000)

Try this worksheet function:

=IF(A1<=37621,"Formula A","FormulaB")

the value 37621 is the DateValue for 31/12/02 or as we yanks write it 12/31/02.

5. ## Re: IF and Date (Excel 2000)

Or use:
<pre>=IF(A1<DATEVALUE("1/1/03"),<formula A here>,<formula B here>)
</pre>

and it doesn't matter what the regional settings are.

Steve

6. ## Re: IF and Date (Excel 2000)

1/1/03 will be interpreted as January 3, 2001 in some countries (Sweden, Lithuania, Latvia).

To avoid problems with regional settings, it's best to store the date in an auxiliary cell (letting Excel/Windows do the conversion), or use the DATE(year, month, day) function.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•