Conditional Format by date
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
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 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
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 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
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 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

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
  •  

 

 
DMCA.com