Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Confusing date test (Excel 2000)

    I am testing a cell that has a date that is not in text format, in Cell L2, with a value of 5/1/2002 0:00. In another cell I say L2>9/11/01 and get TRUE. In a second cell I say L2<05/06/2003 and get FALSE, where it should say true (5/2002<5/2003). I can't understand why. The overall goal is to choose a depreciaton method based upon whether the Date placed in service is a) less than 9/11/01 b)between 9/11/01 and 5/6/03 c) between 5/7/03 and 12/31/04, or d) after than 12/31/04. What would be the best overall formula to say method A method B method C or Method D? Choose? Nothhing will work unless I get that date logic figured out. As always, thanks.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Confusing date test (Excel 2000)

    Hi,
    I would use a lookup table for this (see attached). Your current problem is that Excel is not viewing your parameters as dates but rather as 9 divided by 11 divided by 1.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    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: Confusing date test (Excel 2000)

    Try:
    =L2 >datevalue("9/11/01")

    =L2<datevalue("05/06/2003")

    rory's suggestion gets around the need for "datevalue" and will work better than a string of IFs...

    Steve

  4. #4
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Confusing date test (Excel 2000)

    OK...I see how both work to solve my question..thanks. I will work on an "in-cell" solution, though, because I may need to have the formula "portable" to others in an efficient way, without a lookup function involved. I will work on it.

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Confusing date test (Excel 2000)

    Hi,
    If you want the lookup in-cell then you can use:
    <pre>=LOOKUP(A1,{0,"Method1";37204,"Method2";37748 ,"Method3";38353,"Method4"})</pre>

    though I would recommend the table as it's easier to maintain and to see what's going on!
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Confusing date test (Excel 2000)

    I see braces within the formula. Not familiar with them in that construction. I thought they were array-formla punctuation.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Confusing date test (Excel 2000)

    You can use the {} construction to indicate an array of values too (in this case a 4 row, 2 column array). Lookup will accept either an array or a range as its second argument - the range is effectively an array of values anyway.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Confusing date test (Excel 2000)

    Very interesting! Thank you.

Posting Permissions

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