+ Reply to Thread
Results 1 to 25 of 25

Changing Cell Colour set days before a date and set days after a date.

  1. #1
    Registered User
    Join Date
    04-16-2013
    Location
    Birmingham
    MS-Off Ver
    Excel 2003
    Posts
    21

    Changing Cell Colour set days before a date and set days after a date.

    Hello everyone,

    Been introduced to this forum by a friend of mine who says it is the place to go when faced with the difficulties of being useless at excel but want to learn how to make it do what you want.

    This is what I need certain cells in a data range to do.

    I send out correspondence to customers setting a reply by date. At the moment I check my dates daily and when I'm approximately two days away from the reply by date I highlight those dates in yellow indicating they are running close to the date. When the date is today I change the field to orange to show items I need to work on. When it is two days past the reply date I highlight them red indicating reminders need to be issued.

    I need to set a conditional formatting formula which caters for this and no matter whether I try the TODAY or NOW function I just cant seem to get it to work. PLEASE HELP!

    Heres an example:

    I issue correspondence for a customer requesting contact by 10/05/2013.
    On the 08/05/2013 (two days prior to this) I would like the cell colour to change to yellow
    On the 10/05/2013 (day the reply date was set for) I would like the cell colour to change to orange
    On the 12/05/2013 I want the cell colour to change red

    However, is there any way to set a control whereas if a reply is received I can indicate this on the form so no colours are activated. I suppose I can manually remove the conditional formatting for the cell if there is no easy way to do that as otherwise all dates will eventually turn and stay red.

    Please help if you can.

    Warmest Regards

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Changing Cell Colour set days before a date and set days after a date.

    Hi and welcome to the forum, you have come to the right place for help

    I often "practice" CF formulas on the actual worksheet 1st, to make sure they work - formulated to return a TRUE or FALSE answer. So, with your data, and assuming your date is in A1...

    =today()+2=A1
    =today()=A1
    =today()-2=A1

    With that "foundation", you should be able to get what you need, If you still have a problem, i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    05-22-2012
    Location
    IL
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Changing Cell Colour set days before a date and set days after a date.

    Hi imranrasool, I'm not familiar with 2003 capabilities, but if you can use formulas with conditional formatting, this one works for me: Within conditional formatting select use a formula to determine what cells to format and apply to the desired range.
    =IF(AND(A1<>"DONE",B1>=TODAY()-2),TRUE), considering your "Done" column is A and your dates are in column B. Change today()-2 to today() for orange and today()+2 for red.

  4. #4
    Registered User
    Join Date
    04-16-2013
    Location
    Birmingham
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: Changing Cell Colour set days before a date and set days after a date.

    Hi FDibbins and mmogharreban

    Thank you for replying to my problem. I apologise for the delay in replying but it is a work related issue and have only just got access to my work pc. I will attempt the solutions you have provided now and fingers crossed they do work.

    If any issues I will upload the worksheet this relates to and enter some dummy data for you to consider. I apologise in advance for the layout and design which can be described as amateurish at best and accpet it is probably nowhere near the standards to which you guys are able to create them.

    Speak shortly.

  5. #5
    Registered User
    Join Date
    04-16-2013
    Location
    Birmingham
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: Changing Cell Colour set days before a date and set days after a date.

    Hi FDibbins

    I have begun by using your solution and it certainly seems to do almost everything I wanted.

    Two days prior to the deadline it is turning yellow...BRILLIANT
    On the day of the deadline it is turning orange...BRILLIANT
    Two days after the deadline it is turning red..BRILLIANT

    However the problem is it changes to blank in between.

    For example say the deadline date is 20/04/13
    On the 18/04/13 it turns yellow
    On the 19/04/13 it reverts to blank as the condition is set for two days before, how would I ask it to remain yellow?
    On the 20/0413 it turns orange
    On the 21/04/13 it turns blank again, how do i set it so it remains orange until the next condition?
    On the 22/04/13 it turns red
    On the 23/04/13 it turns blank again. How would I set it to remain red as want it to remain red until I can place a comment saying it has been done?

    If that doesnt make sense please advise.

    Kindest of Regards

    Imran

  6. #6
    Registered User
    Join Date
    04-16-2013
    Location
    Birmingham
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: Changing Cell Colour set days before a date and set days after a date.

    Quote Originally Posted by mmogharreban View Post
    Hi imranrasool, I'm not familiar with 2003 capabilities, but if you can use formulas with conditional formatting, this one works for me: Within conditional formatting select use a formula to determine what cells to format and apply to the desired range.
    =IF(AND(A1<>"DONE",B1>=TODAY()-2),TRUE), considering your "Done" column is A and your dates are in column B. Change today()-2 to today() for orange and today()+2 for red.
    Hi mmogharreban

    Unfortunately I think I have misunderstood your formula and not sure if I am applying it right as unsure what Done and B1 represent as the dates are in the middle of the spreadsheet I will now upload an empty one with the formula I have input thus far from FDibbins and request some further input at your convenience.

  7. #7
    Registered User
    Join Date
    04-16-2013
    Location
    Birmingham
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: Changing Cell Colour set days before a date and set days after a date.

    Quote Originally Posted by mmogharreban View Post
    Hi imranrasool, I'm not familiar with 2003 capabilities, but if you can use formulas with conditional formatting, this one works for me: Within conditional formatting select use a formula to determine what cells to format and apply to the desired range.
    =IF(AND(A1<>"DONE",B1>=TODAY()-2),TRUE), considering your "Done" column is A and your dates are in column B. Change today()-2 to today() for orange and today()+2 for red.

    Hi mmogharreban,

    Unfortunately due to work restrictions its not allowing me to upload any files with an admin password which I'm not so I'll try my best to describe what I need to do.

    Okay so say Cell A1 has the date of reply expected for a letter.
    I issue a letter allowing three weeks for the person to reply. In cell A1 I enter the date i.e 30/04/13
    As before two days prior to 30/04/13 I want the cell colour to change yellow and stay yellow until the date of reply.
    On the date of reply I want it to change to orange and then stay orange until two days after the date of reply.
    Two days after the date of reply I want it to change red and stay red

    Hope that makes sense. If I can help in any way please do let me know although I am well out of my depth on this forum as indicated by the previous responses.

  8. #8
    Registered User
    Join Date
    05-22-2012
    Location
    IL
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Changing Cell Colour set days before a date and set days after a date.

    Hi again...this solution would require you to create 3 separate conditional formats. Select your range containing the dates (A1 per your above). Within conditional format, change the field "Cell Value Is" to "Formula Is" and first enter:

    =IF(AND(A1>=TODAY()-2,B1<>"Y"),TRUE). "Y" can be replaced with whatever you choose to note that the letter has been "received" (DONE)

    This formula will check whether or not the date in A1 is greater than or equal to 2 days from today AND whether or not B1 contains a "Y"...meaning Yes received or Not received as you stated in your original post. So this condition will activate only if both criteria are met. Once you select your formatting options(YELLOW), create another with

    =IF(AND(A1<>"DONE",B1>=TODAY()),TRUE), for the ORANGE cell, and then another
    =IF(AND(A1<>"DONE",B1>=TODAY()+2),TRUE) for the RED.

    So if you have a "Y" in the B column, no formatting will show.

    Hope this helps

  9. #9
    Registered User
    Join Date
    05-22-2012
    Location
    IL
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Changing Cell Colour set days before a date and set days after a date.

    I've attached an example. One additional note, the Red condition needs to listed first, followed by the orange, and finally the yellow...due to some limitations with 2003. Otherwise, once the cell turns yellow, it will remain yellow as all other conditions will stop running.

    Copy.xls

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Changing Cell Colour set days before a date and set days after a date.

    If you want the CF to be restricted to 1 or 2 days before, and 1 or 2 days after, then try this. Its a bit messy but does what you want. double-up on the formula for each test, using OR...

    =OR(TODAY()+1=A6,TODAY()+2=A6)
    use the same method for the other 1
    =OR(today()-1=A1,=today()-2=A1)

  11. #11
    Registered User
    Join Date
    04-16-2013
    Location
    Birmingham
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: Changing Cell Colour set days before a date and set days after a date.

    Hi again,

    I think I may not have explained properly what I need as want the colour changing dependent on the date in the cell which can vary betwee now and several months in the future. I have downloaded the sheet kindly uploaded by mmogharreban and tried the formulas suggested by FDibbins but dont think they are correct for what I wanted.

    As I cant upload spreadsheets I'll show you the three columns I am referring to.

    Column A Column B Column C Column D
    A1 Date B1 Action C1 Reply By D1 Reponse
    A2 17/04/2013 B2 1st Letter C220/05/2013 D2 Y or N
    A3 17/04/2013 B3 1st Letter C329/04/2013 D3 Y or N

    So I send letters out on the 17/04/2013 and expect a reply by the dates above. on the first entry. On the 18/05/2013 I automatically want that cell to turn yellow and remain yellow until the 20/05/2013 at which point it turns orange. On the 22/05/2013 I want it to turn red. At any point I will enter a Y if a reply comes in if not I leave it blank and sent a follow up. I have input the columns and cell references in my example above as am getting abit confused with the formulas so if you kindly use the above and then I can work them out at my end.

    The formulas I have at the moment, change to red the moment I input a future date. They only work in restriction to todays date, where as I want to work in relation to the date in the actual cell.

    Am so sorry I'm making a hash of explaining it but hopefully that makes sense. Can only apologise for using so much of your time but am so grateful.

  12. #12
    Registered User
    Join Date
    05-22-2012
    Location
    IL
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Changing Cell Colour set days before a date and set days after a date.

    Try this...I used cell F1 to reference today() so you can play with the dates for testing.

    Copy.xls

    Put these into conditional formatting:
    =IF(AND(C2<=TODAY()-2,D2<>"Y"),TRUE) Red
    =IF(AND(C2<=TODAY(),D2<>"Y"),TRUE) Orange
    =IF(AND(C2<=TODAY()+2,D2<>"Y"),TRUE) Yellow

  13. #13
    Registered User
    Join Date
    04-16-2013
    Location
    Birmingham
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: Changing Cell Colour set days before a date and set days after a date.

    Quote Originally Posted by mmogharreban View Post
    Try this...I used cell F1 to reference today() so you can play with the dates for testing.

    Attachment 228820

    Put these into conditional formatting:
    =IF(AND(C2<=TODAY()-2,D2<>"Y"),TRUE) Red
    =IF(AND(C2<=TODAY(),D2<>"Y"),TRUE) Orange
    =IF(AND(C2<=TODAY()+2,D2<>"Y"),TRUE) Yellow
    Hi again, Think am finally getting somewhere with this. Thank you so much for your input. It would seem the formulas are now working. Only issue I now have is when I copy and paste it, it automatically makes any cells with no entries red.

    If you try it on your copy.xls spreadsheet you will see what I mean is there anyway to stop it from doing that?

  14. #14
    Registered User
    Join Date
    05-22-2012
    Location
    IL
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Changing Cell Colour set days before a date and set days after a date.

    Well that's interesting. I think this is a result of the UK dating format...not sure why. Try this...

    =IF(AND(C2<>"",C2<=TODAY()-2,D2<>"Y"),TRUE) Red
    =IF(AND(C2<>"",C2<=TODAY(),D2<>"Y"),TRUE) Orange
    =IF(AND(C2<>"",C2<=TODAY()+2,D2<>"Y"),TRUE) Yellow

  15. #15
    Registered User
    Join Date
    04-16-2013
    Location
    Birmingham
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: Changing Cell Colour set days before a date and set days after a date.

    Also just a slight modification, at the moment the formula is set so that is clears the cell colour if theres a Y in the adjacent box.

    Is there a way to make it clear if anything is input the box instead. So maybe not a y, a comment or a date?

    Apologies this query has expanded but the ideas are flowing once i tasted the possibilities.

  16. #16
    Registered User
    Join Date
    04-16-2013
    Location
    Birmingham
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: Changing Cell Colour set days before a date and set days after a date.

    Quote Originally Posted by mmogharreban View Post
    Well that's interesting. I think this is a result of the UK dating format...not sure why. Try this...

    =IF(AND(C2<>"",C2<=TODAY()-2,D2<>"Y"),TRUE) Red
    =IF(AND(C2<>"",C2<=TODAY(),D2<>"Y"),TRUE) Orange
    =IF(AND(C2<>"",C2<=TODAY()+2,D2<>"Y"),TRUE) Yellow

    Hiya I have tried this and this eliminates the issue when copying and pasting the CF.

    But it has thrown up another problem it doesnt recognise beyond the set values.

    for example say reply by date is 20/04/13.
    On the 18/04/13 it turns yellow on the 19/04/13 it turns blank.
    On 20/04/13 it turns orange then on the 21/04/13 it turns blank
    On the 22/04/13 it turns red and then beyond that it stays red.

    I do feel terrible i keep coming back but the CF is so complicated for me now dont know how to address this. It was working perfectly prior to trying to adjust the issue with the blank cells changing colour when copying CF's.
    Last edited by imranrasool; 04-18-2013 at 10:19 AM.

  17. #17
    Registered User
    Join Date
    05-22-2012
    Location
    IL
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Changing Cell Colour set days before a date and set days after a date.

    =IF(AND(C2<>"",C2<=TODAY()-2,D2<>""),TRUE) will activate if D2 is not blank. (<> means [I]is not[I] "" means blank)

  18. #18
    Registered User
    Join Date
    04-16-2013
    Location
    Birmingham
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: Changing Cell Colour set days before a date and set days after a date.

    Hi mmogharreban,

    Thanks for that but it doesnt work the way I wanted it, its the opposite.

    For example, now the colour in C2 doesnt change dependent on the conditions of the date. Instead I also need to input something.

    So say date of reply is 20/04/13 on the 18/04/13 it was turning yellow now it doesnt. But if I input something in D2 it does.

    Now just have to work out how for it to remain the same colour past the set period.

    i.e 2 days prior to reply want it to go yellow but also stay yellow 1 day to go
    on the day of reply want it to turn orange but also stage orange 1 day after
    2 days after date of reply want it to turn red and stay red (think this is already happening).

    I think I have used up more than enough of everyones time, I dont think its fair to keep coming back and asking further questions. I'm sorry for being a pain but if it could be made to work it would make my work alot easier to organise.
    Last edited by imranrasool; 04-18-2013 at 11:23 AM.

  19. #19
    Registered User
    Join Date
    05-22-2012
    Location
    IL
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Changing Cell Colour set days before a date and set days after a date.

    Ok...I think there are some challenges with the date formatting that I am unfamiliar with (I'm definitely no expert).

    This works for me

    =IF(AND(C2<>"",C2>=TODAY()+2,D2<>""),TRUE)
    =IF(AND(C2<>"",C2>=TODAY(),D2<>""),TRUE)
    =IF(AND(C2<>"",C2>=TODAY()-2,D2<>""),TRUE)

  20. #20
    Registered User
    Join Date
    05-22-2012
    Location
    IL
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Changing Cell Colour set days before a date and set days after a date.

    Sorry! My fault!

    =IF(AND(C2<>"",C2>=TODAY()+2,D2=""),TRUE)
    =IF(AND(C2<>"",C2>=TODAY(),D2=""),TRUE)
    =IF(AND(C2<>"",C2>=TODAY()-2,D2=""),TRUE)

  21. #21
    Registered User
    Join Date
    04-16-2013
    Location
    Birmingham
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: Changing Cell Colour set days before a date and set days after a date.

    Quote Originally Posted by mmogharreban View Post
    Sorry! My fault!

    =IF(AND(C2<>"",C2>=TODAY()+2,D2=""),TRUE)
    =IF(AND(C2<>"",C2>=TODAY(),D2=""),TRUE)
    =IF(AND(C2<>"",C2>=TODAY()-2,D2=""),TRUE)
    Hiya mate,

    Sorry to be yet further pain. This only works in reference to todays date. So today is 18/04/13. 16th and 17th make it go orange. 18th and 19th make it go orange. 20th and beyond make it go red.

    I want it to change colour dependent on the date in the actual cell. So that can be a date anytime between now and the future. When the date in the cell is being approached I want it to change the cells accordingly.

    the colours work and the bit for when something is entered also works. Can you perhaps try it on a spreadsheet and you'll see what i mean. If you get it working can you please attache the spreadsheet.

    Thanks again.

  22. #22
    Registered User
    Join Date
    05-22-2012
    Location
    IL
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Changing Cell Colour set days before a date and set days after a date.

    Cells in col C change when today's date comes within range of date placed in col C. Yellow if today's date is within 2 days of date in col C. Orange if today's date is equal to or greater than the date in col C(which will override yellow). Red if todays date is greater than or equal to 2 days past the date in col C (overrides yellow and orange). You might need to tweak a bit with UK date format differences. This one is working for me...

    Copy.xls

  23. #23
    Registered User
    Join Date
    04-16-2013
    Location
    Birmingham
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: Changing Cell Colour set days before a date and set days after a date.

    Looks like I will have to continue this journey when I get home. As work have now disabled the ability to download attachements and to request access to it will take forever.

    I will hopefully see if I can get it working when I leave in the next two hours and take it from there.

    I can only thank you for your help thus far and your efforts which have been immense.

  24. #24
    Registered User
    Join Date
    04-16-2013
    Location
    Birmingham
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: Changing Cell Colour set days before a date and set days after a date.

    Quote Originally Posted by mmogharreban View Post
    Cells in col C change when today's date comes within range of date placed in col C. Yellow if today's date is within 2 days of date in col C. Orange if today's date is equal to or greater than the date in col C(which will override yellow). Red if todays date is greater than or equal to 2 days past the date in col C (overrides yellow and orange). You might need to tweak a bit with UK date format differences. This one is working for me...

    Attachment 229113
    Hiya mate,

    Have just downloaded your sheet and it unfortunately isnt correct. I'm not sure whether I'm explaining it wrong

    You'll see on your sheet anythign with a reply by date beyond todays date is already red??

    I want any future dates to remain clear until the rules kick in. i.e yellow when approached two days prior, orange when on the day or day after, or red after todays date is 1 and 2 days after??

    Does that make it any clearer?

  25. #25
    Registered User
    Join Date
    08-29-2012
    Location
    South Yorks, England
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: Changing Cell Colour set days before a date and set days after a date.

    Hi Imranrasool

    Try editing your CF formula to

    =IF(AND(C2<=TODAY()+2,(NOT(D2<>""))),TRUE)

    This should make the cell turn yellow as normal, except if something (anything) is input into cell D2. The formula now reads 'if not blank' instead of 'if Y'

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1