Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Conditional Format by date

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    How do I get a column of dates to turn red if they are more than 3 days older than the current date? I'm sure I've seen the answer to this somewhere on here before but I cant seem to find it

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,655
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-26 01:21, Pankman wrote:

    How do I get a column of dates to turn red if they are more than 3 days older than the current date? I'm sure I've seen the answer to this somewhere on here before but I cant seem to find it
    Lets say that the dates are in A2:A10.

    Activate Format|Conditional Formatting.
    Choose 'Formula Is' for 'Condition 1'.
    Enter in the formula box:

    =A1+3<=TODAY()

    Activate Format.
    Select red on the Patterns tab.
    Click OK, OK.


    [ This Message was edited by: Aladin Akyurek on 2002-03-26 01:41 ]

  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Pankman

    You will need to use
    =A1=TODAY()+3

    Or

    =A1=TODAY()-3

    Depending on your need. If you just use:
    =A1<=TODAY()
    All dates, equal to or less that the current date will be red.









  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the replies, but they didn't seem to work. You see, I have a userform that people fill in, and one of the fields is date, formatted as "dd mmmm yyyy". When they click the command button at the end, the information goes into a sheet of the workbook. Now this info builds up and builds up, so what I need to do is earmark the ones that are older than others, so if the date in the date field is 3 days old or more, then I want it to go red. Would any of this be helpful in figuring out why it didn't work?

  5. #5
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You are probably entering the dates as Text.

    Use:

    Range("A1")=Textbox1.Value

    Just ensure your cells are pre-formatted as the date format you want.



  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,655
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-26 01:45, Pankman wrote:
    Thanks for the replies, but they didn't seem to work. You see, I have a userform that people fill in, and one of the fields is date, formatted as "dd mmmm yyyy". When they click the command button at the end, the information goes into a sheet of the workbook. Now this info builds up and builds up, so what I need to do is earmark the ones that are older than others, so if the date in the date field is 3 days old or more, then I want it to go red. Would any of this be helpful in figuring out why it didn't work?
    Did you try the formula in my edited reply?


  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Aladin, the formula looks great except that I need the formatting to be for the whole of column A, not just A1. How would I do this?

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,655
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-26 02:03, Pankman wrote:
    Hi Aladin, the formula looks great except that I need the formatting to be for the whole of column A, not just A1. How would I do this?
    Did I forget to say that you need to select the range of interest before you activate Format|Conditional Formatting? If so, that should be the first step in the procedure I described.

    Aladin

  9. #9
    New Member
    Join Date
    Mar 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin, that is absolutely perfect!! Thank you

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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