IF and Date (Excel 2000)

Topic

New Reply

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 B

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

Reply | Quote

Replies

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

=IF(A1<=B1,,)

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

=IF(A1<=DATE(2002,12,31),,)

or

=IF(A1<=DATEVALUE("31/12/02"),,)

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Or use:

=IF(A1<DATEVALUE("1/1/03"),,)

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

Steve

Reply | Quote

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.

Reply | Quote