Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Dates in Formulas (Excel 97)

    Hello,

    I'm encountering a problem.

    I have a list of dates in column S

    The criteria is as follows
    if the date is before 12/31/02 then blank
    if the date is after 12/29/03 than 2004
    all else 2003

    I entered the following fomula

    =IF(S2<"12/31/2002","",IF(S2>"12/29/2003","2004","2003"))

    I get all blanks. I'm not all that familiar with how to work dates in formulas. Can someone give me a push in the right direction?

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Dates in Formulas (Excel 97)

    Hi,

    You can't use dates directly in formulae. Try:
    =IF(S2<DATEVALUE("12/31/2002"),"",IF(S2>DATEVALUE("12/29/2003"),2004,2003))

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Dates in Formulas (Excel 97)

    That worked!

    Thank you so very much!

  4. #4
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Dates in Formulas (Excel 97)

    Well, I am now finding inaccurate data.

    The fomula I am using in VBA is:
    "=IF(RC[-3]<DATEVALUE(""12/31/2002""),""2002"",IF(RC[-3]>DATEVALUE(""12/29/2003""),""2004"",""2003""))"

    I have the following dates reporting as 2004:
    1/6/03
    1/22/03
    12/30/02

    12/28/02 is coming back as 2004...

    Hmmm, anyone have any help?

  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: Dates in Formulas (Excel 97)

    This doesn't answer your question, but why not just use the formula:
    <pre>=year(s2)
    </pre>


    Steve

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates in Formulas (Excel 97)

    That formula works fine for me if the cells contain dates. However, if the cells contain text strings that look like dates, then the formula will always return 2004.
    Legare Coleman

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates in Formulas (Excel 97)

    I thought of that also, but if you look at the formula closely, it returns 2004 for the last two days of 2003. If that is what is really wanted, your formula does not work.
    Legare Coleman

  8. #8
    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: Dates in Formulas (Excel 97)

    Legare,
    Good Catch on both counts!
    That's what I get for trying to guess what they wantrather than what they ask for.
    I am surprised that a text value is greater than a date. I thought the text would be "0" and < date.

    It must be compared as text AND it must use as TEXT for the number a formatted value.

    Both seem odd to me, though I have given up trying to understand all the quirks in excel.

    Steve

  9. #9
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Dates in Formulas (Excel 97)

    I can't use the last 2 numbers in the year because 2002 ends on 12/30/02 (Therefore anything done on or before 12/30/02 falls in 2002)
    2003 starts on 12/31/03 and ends on 12/29/03. Anything on or after 12/30/03 falls in 2004.

    I just reentered the formula directly into a cell and it works fine, it seems to be a problem in VBA. Does the DATEVALUE work differently in VBA?

    Directly in Cell:
    =IF(S2<DATEVALUE("12/31/2002"),"2002",IF(S2>DATEVALUE("12/29/2003"),"2004","2003"))

    VBA:
    Range([t2], [t2].End(xlDown)).Offset(0, 2).FormulaR1C1 = _
    "=IF(RC[-3]<DATEVALUE(""12/31/2002""),""2002"",IF(RC[-3]>DATEVALUE(""12/29/2003""),""2004"",""2003""))"

  10. #10
    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: Dates in Formulas (Excel 97)

    Putting this in V2 and copying it down
    =IF(S2<DATEVALUE("12/31/2002"),"2002",IF(S2>DATEVALUE("12/29/2003"),"2004","2003"))

    Is identical to the VB code.

    The problem is (as Legare had mentioned) is that some of your "dates" In col S are NOT dates but TEXT that looks "like a date"

    Steve

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates in Formulas (Excel 97)

    The text string probably forces a byte by byte comparison of the characters in the date string to the binary bytes of the date value. The first that are not equal sets the result.
    Legare Coleman

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates in Formulas (Excel 97)

    I used VBA to enter the formula and as long as what is in RC[-3] is a date value, it works. However, if what is in RC[-3] is text that looks like a date, then I get the results that you describe.
    Legare Coleman

  13. #13
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Dates in Formulas (Excel 97)

    I have found the error of my ways.. I am to embarrased to tell you what I did, but I will say it wasn't because of text in my column... <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    I hate when I make stupid mistakes, they are so time consuming!

  14. #14
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Dates in Formulas (Excel 97)

    Oops.. I fogot to thank you guys for all of you help!

    Thank you!

  15. #15
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates in Formulas (Excel 97)

    Everyone would really appreciate knowing what the problem was so we can diagnose or avoid the same problem in the future.
    Legare Coleman

Page 1 of 2 12 LastLast

Posting Permissions

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