Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date formula problem (Excel 2000)

    Hi again folks, have one here that I'm stumped on. If I want to take Column A and create a conditional format on that column to show me if any date typed in that column is exactly one year earlier then the current date, (by turning the font red or something similar), what expression would I type into the conditional format "Formula Is" command line? I sure would appreciate any help on this you can give me.
    Thanks so very much,
    As Always,
    NMPadgett
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Date formula problem (Excel 2000)

    I may have this backwards, but you'll get the drift. Where C4 contains the old date and C5 the 'new' date and the conditional formatting:

    =DATE(YEAR(C5)-1,MONTH(C5),DAY(C5))=C4
    -John ... I float in liquid gardens
    UTC -7ąDS

  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: Date formula problem (Excel 2000)

    Try this

    =int(A1)=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))

    Steve

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date formula problem (Excel 2000)

    I have tried this formula, and I have tried the next post formula, and neither is giving me anything close to what I'm asking for I'm afraid. If I highlight column A, choose Format conditional formatting, I need to put a formula into the first conditional box that states this: If a user types any date into column A that is anything older then one year from the current date then I want the font in the cell to turn red. Does that make sense? Like this: If DATE typed in cell is < TODAY(), then turn cell red
    Thanks, and appreciate any feedback.
    NMPadgett
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date formula problem (Excel 2000)

    Try this:
    In Conditional formatting, pick "Formula is" and enter the following:
    <pre>=DATEDIF(A1,NOW(),"M")>=12</pre>


    where A1 is the address of the first cell you're using, then specify the Format to have red font color.
    Then copy this cell down the A column. The A1 address is relative, so always refers to the current cell (ie in cell A2 the formula will contain A2 etc)

    Have fun!
    Ian.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date formula problem (Excel 2000)

    Bless you!
    That was it, and works like a charm...I salute you! <img src=/S/salute.gif border=0 alt=salute width=15 height=20>
    NMPadgett
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Date formula problem (Excel 2000)

    I'm sorry, in your original post you stated "exactly one year after", and I took it literally meaning one year to the day, neither more nor less, (and I think Steve did also, but his interpretation was exactly one year to the day before 'today'), rather than a year or more older. C'est la vie! <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  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: Date formula problem (Excel 2000)

    Yes, I agree with John's statement. I gave you what you asked for, it might NOT have been what you WANTED, but it was what you asked:
    "I want to take Column A and create a conditional format on that column to show me if any date typed in that column is exactly one year earlier then the current date"

    =int(A1)=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))

    will do that. If today is 9/13/2002, and you had the above conditionally formatting set. When you would have 9/13/2001, that date condition would be TRUE. Tomorrow on 9/14/2002, it would be FALSE.

    =int(A1)<DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))
    would be true for any day OLDER than 1 year from today.

    You also could use:
    =DATEDIF(A1,NOW(),"y")>=1

    or even:
    =now()-A1 > 365
    (I wouldn't worry about leap years if I only cared about "more than a year")

    As John put it, C'est la vie!
    Steve

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date formula problem (Excel 2000)

    You are absolutely right. I am sorry. I did say "exactly", and I shouldn't have. Thanks for hanging in there with me as always, and do appreciate all the feedback, and all the suggestions. You outdo yourselves.
    NMPadgett
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

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