+ Reply to Thread
Results 1 to 8 of 8

Change colour in column C based on Column B - future dates

  1. #1
    Registered User
    Join Date
    06-21-2013
    Location
    Blackpool
    MS-Off Ver
    Excel 2003
    Posts
    4

    Change colour in column C based on Column B - future dates

    Hi new and been dealt a task by husband I am struggling with.

    I have a spreadsheet (attached) and the dates in column B are dates due (these may change - this is just a template) I think I have set a format to change colour when they are due and + 7 days due. When the date in column C is inputted, if it is the same as the due date I want both B and C columns to be white, if C is later than the due date, I want it to be red.

    Can this be done?

    Feeling thick!

    Thanks

    Kate Attachment 244852
    Attached Files Attached Files
    Last edited by kfwiss; 06-21-2013 at 05:03 PM.

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Please help - probably simple!

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
    HTH
    Regards, Jeff

  3. #3
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Change colour in column C based on Column B - future dates

    If I read your requirements correctly, these two conditions will work...

    =$C6>$B6 <-- Red
    =$B6=$C6 <--White

  4. #4
    Registered User
    Join Date
    06-21-2013
    Location
    Blackpool
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Change colour in column C based on Column B - future dates

    Jeff, thank you so much - I have so much to learn and thanks for your great and quick advice. Worked perfectly.
    When I get stuck again - no doubt............I will be back.
    Thanks again

  5. #5
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Change colour in column C based on Column B - future dates

    it can and you have applied with NOW()
    as it does not need a time - try using today()

    if no date
    you have a condition for +30

    so you mention date entered into C
    so what happens when C is blank and date passes

    I would use the formula section
    just need the rules

    1) C is blank and date in B is later then today AND( C2="", B2>today())
    2) C is blank and date in B is upto 7days of today AND( C2="", B2>today()-7)
    3) C is blank and date in B is before 7days of today AND( C2="", B2<today-7())

    Now what happens if a date is entered into column C

    do you want to highlight if date is later or 7 days ?

    1) date entered is 7 days later than the due date B2<C2-7
    2) date entered is same as the due date B2=C2 - so no cond format required

    Not sure if i'm allowed to answer until you have carried out the instructions from @jeffreybrown

    so sorry if i'm not supposed to - please advise

  6. #6
    Registered User
    Join Date
    06-21-2013
    Location
    Blackpool
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Change colour in column C based on Column B - future dates

    Thank you for your help Etaf - I am really finding this hard. I thought the original post had helped but then I realised I am worse at Excel than I thought! What you said makes sense re date passing but I cant seem to get what you have posted to work for me??

    Am I being stupid - if someone paid on the day: would the days all be white? Can I just not see it as they are in the future?

    Feeling so embarrassed now :-(

  7. #7
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Change colour in column C based on Column B - future dates

    no need to be embarrassed

    just need to know what you need to do really and then conditional format for those conditions

    and i may be confusing you further anyway - Its late in UK - so i may sign off soon and review tomorrow

    then break down in steps the rules

    All white is usually left as the default and not conditional format - so the rules assume conditions for NOT all white

    so first question - what happens if no dates are entered into column C - do you care about tracking overdue or near due dates - and want any colours to apply - based on today() todays date ?

    if not and you are only interested in comparing column C and B
    then what are each of the rules

    B6=C6 - format white - so no need to apply this rule assumed to be default condition

    so now what are the rules

  8. #8
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Change colour in column C based on Column B - future dates

    i thought I would have a play this morning (uk time) and set up some conditions

    if they are all wrong - don't worry about it - I wanted to have a play any way

    the conditions are

    In Column B

    =AND(C6="",B6<TODAY())

    so if the date in column B is less than today - fill in B column will turn red - ie payment is now over due

    In column C three conditions

    =AND(C6<>"",C6<B6)
    if C6 is not blank - and C6 date is before the due date - turn green

    =AND(C6>B6, C6<B6+7)
    if C6 date is after the payment due but before or equal to 7 days - turn orange

    =C6>B6+7
    if c6 date is greater than the payment due by more than 7 days - turn red

    And the default - all white - is if the due date and the pay date are the same day

    see attached sheet

    Note I have changed a lot of the due dates in column B to test the conditions work (highlighted Cell A yellow - where I changed the dates in B )

    This will work in any future templates

    The cells the conditional format applies to - is up to row 35
    if you need to go above that row - then all the conditional format - Applies to range , just needs the row numbers extended - but when you do that make sure the formulas do not also change row number

    I dont know if you applied the other conditional formats -I have removed them all except this one , also the conditional format set used NOW() which will also look at the system time

    =LEN(TRIM(L1))>0 - which applied to L1 to O2
    and made the font bold

    see attachment

    Please do not worry - if its all wrong - or if you want to make minor changes - just ask here and we can get exactly how you want the spreadsheet to operate
    Attached Files Attached Files

+ 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