Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    London, United Kingdom
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    London, United Kingdom
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •