Conditional formatting on specifica date ranges
Results 1 to 8 of 8

Thread: Conditional formatting on specifica date ranges
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member SelinaR's Avatar
    Join Date
    Feb 2012
    Location
    Perth, Australia
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Conditional formatting on specifica date ranges

    Hi there

    I would like to put in cond. formatting on date ranges that will pick up from TODAY() - I have tried various options - pre set on excel and my own formula
    of greater than, or equal to such as =TODAY() & =NOW() & DATEDIF, etc, but seem to be getting blank zones within the range and my formula's specific to today vs. 28 days from now . Please help - I am looking for more of a range that will format rather than an actual date to highlight

    Example of rules (all feeding off of today &/or an input date):

    1st Within 28 Days (current)
    2nd After 28 days (going to lapse)
    3rd >29 days & up to 3 months
    4th over 3 Months
    5th >6 Months

    Thanks so much for your help in advance.
    Selina

  2. #2
    Board Regular
    Join Date
    Jul 2015
    Posts
    354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional formatting on specifica date ranges

    Is that you have a sheet of calendar, and subject to your input date, you would like Excel to CF those calendar dates according to your specified criteria?

  3. #3

    Join Date
    Sep 2015
    Posts
    130
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional formatting on specifica date ranges

    rule 1: highlight with yellow

    Code:
    =AND((D$3-TODAY())<=28,(D$3-TODAY())>=0)



    rule 2 : - i did not understand it

    rule 3: highlight with purple

    Code:
    =AND((D$3-TODAY())>29,(D$3-TODAY())<90)



    rule 4: highlight with green

    Code:
    =(D$3-TODAY())>=90



    rule 5: highlight with blue

    Code:
    =(XFA$3-TODAY())>180



    all your conditional formatting rules should be referenced on the desired range.

    use a relative reference in the formula,, no $ sign on column letter.

  4. #4
    New Member SelinaR's Avatar
    Join Date
    Feb 2012
    Location
    Perth, Australia
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional formatting on specifica date ranges

    Hiya thanks for that - its more like this:

    scenario - have to contact a member within certain dates of a return to sender mail (not network days - total days)
    1st Within 28 Days (current) - green
    2nd After 28 days (Up to maximum of 28 days from today's date) - orange
    3rd over 29 days & up to 90 days - red
    4th =>91 days to over 180 days - bright red.
    5th =>181 days - maroon

    Col A1
    TODAY'S DATE
    =TODAY() i.e 30/11

    Col A Col B Col C
    RTS DATE Priority Callback
    01/12 1 Immediate
    25/12 2 Yes
    20/10 3 Attempt contact
    20/07 4 Note and archive

    *the conditional formatting is reading from: todays' date then the RTS date will have the priority colours

    The conditional format - highlight rules - date occuring is great, but is only locked into this week, next week etc... blocks of time that are outside of my criteria.

    Thanks

  5. #5
    New Member SelinaR's Avatar
    Join Date
    Feb 2012
    Location
    Perth, Australia
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional formatting on specifica date ranges

    *Date on RTA Mail Priority 2nd Method of Contact
    (use drop down/type)
    28/12/2015 Mobile
    25/12/2015 Home no.
    1/12/2015 Email
    30/11/2015 Mobile
    20/11/2015 Home no.
    20/10/2015 Email
    5/09/2015 All
    1/12/2015

  6. #6
    Board Regular
    Join Date
    Jul 2015
    Posts
    354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional formatting on specifica date ranges

    Excel 2007
    ABCDEF
    1RTS DATEPriorityCallback1st rule=(($A2-TODAY())<=27)green
    22015/12/11Immediate 2nd rule=(($A2-TODAY())=28)orange
    32015/12/252Yes3rd rule=AND(($A2-TODAY())>=29,($A2-TODAY())<90)red
    42016/10/203Attempt contact4th rule=AND(($A2-TODAY())>=91,($A2-TODAY())<180)bright red
    52016/7/204Note and archive5th rule=(($A2-TODAY())>=181)maroon

    Sheet2



    Last edited by smallxyz; Nov 30th, 2015 at 03:46 AM.

  7. #7
    New Member SelinaR's Avatar
    Join Date
    Feb 2012
    Location
    Perth, Australia
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional formatting on specifica date ranges

    Hiya

    Thanks for the reply... my example was for the colour coding within ranges, so could you let me know which parameter your using in conditional Formatting:

    CELL VALUE -- EQUAL TO -- and then putting in the formula or
    FORMULA VALUES ?
    *I've tried both options and it doesn't seem to be working for me - have I missed something?

    Your formula looks sound, but is only coming off of a cell reference.... I require it to be against a range and attempted to input that, but not working either?

  8. #8
    New Member SelinaR's Avatar
    Join Date
    Feb 2012
    Location
    Perth, Australia
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional formatting on specifica date ranges

    I have Excel 2010

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
  •