Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Conditional Formatting

  1. #1
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aloha All,

    I have in cell A1, the date an employee has completed a training session. I would like to conditional format the cell to prompt (turn yellow) after 11 months, red after 12 months. Now the hard part (for me)is, they may have been trained on say Mar 15th, but the 11 month period starts from the last day of the month, the 31st of Mar. But the date in A1 must reflect the actual date of the completed training. So the dates in A1 will vary, but the 11 month period would always start from the last day of the month.

    Brian



    [ This Message was edited by: Brian from Maui on 2002-04-02 22:10 ]

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Untested!

    try:

    =AND(NOW()>=DATE(YEAR(A1),MONTH(A1)+12,0),NOW()
    for the yellow condtion.

    and:

    =NOW()>=DATE(YEAR(A1),MONTH(A1)+13,0)

    Hope this helps.

    _________________
    Share the wealth!!
    Ian Mac

    [ This Message was edited by: Ian Mac on 2002-04-03 01:45 ]

    [ This Message was edited by: Ian Mac on 2002-04-03 01:46 ]

  3. #3
    New Member
    Join Date
    Apr 2002
    Location
    Wokingham, England
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I don't know the full solution for this, but perhaps if you tried using some kind of hidden column where it reflected the month in column A but not the actual day. Then used your conditional formatting based from this hidden column rather than the column in view?
    The Mad Druid

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Brian, for some reason I can't see all of the formula I put for the first part,

    I'll try again.

    =AND(NOW()>=DATE(YEAR(A1),MONTH(A1)+12,0).....
    ........,NOW()
    _________________
    Share the wealth!!
    Ian Mac

    [ This Message was edited by: Ian Mac on 2002-04-03 01:48 ]

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    And again.

    can't seem to get this to display, so essential put these two together with an AND function.

    =NOW()>=DATE(YEAR(A1),MONTH(A1)+12,0)
    =NOW()
    also, if you want to highlight the row use:

    =AND(NOW()>=DATE(YEAR($A1),MONTH($A1)+12,0),NOW()
    (hope the above worked) using the $ on the column you want will anchor the other cells to that reference.

    Note: the 12 and 13 may be wrong, could be 11 and 12 respectively.

    Hopefully I've worked the < thing out
    _________________
    Share the wealth!!
    Ian Mac

    [ This Message was edited by: Ian Mac on 2002-04-03 02:53 ]

    [ This Message was edited by: Ian Mac on 2002-04-03 02:54 ]

    [ This Message was edited by: Ian Mac on 2002-04-03 06:33 ]

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

    Default

    Hi Brian


    Looks like you may already have your answer but to get the last day of any month within a date you can use:

    =EOMONTH(A2,0)

    Where A2 holds any date. The EOMONTH is part of the Analysis toolpak, so you may need to go to Tools>Add-ins



  7. #7
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mahalo Guys,

    The time difference here, I just got up. Will try all of these! And it's 4:30 am

    Brian

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

    Default

    On 2002-04-03 06:29, Brian from Maui wrote:
    Mahalo Guys,

    The time difference here, I just got up. Will try all of these! And it's 4:30 am

    Brian
    Not sure but another proposal:

    Use for Condition 1:

    =DATEDIF(A1,TODAY(),"M")=11

    Format yellow.

    Use for Condition 2:

    =DATEDIF(A1,TODAY(),"M")>=12

    Format red.

    Note. Conditional formatting will not accept formulas that use EOMONTH, EDATE, etc.

    Aladin

  9. #9
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin,

    It stays red all the time.

    Brian

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

    Default

    On 2002-04-03 14:55, Brian from Maui wrote:
    Aladin,

    It stays red all the time.

    Brian
    Which date stays red all the time? Do you mean you don't get no yellow while that should be the case?


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
  •