+ Reply to Thread
Results 1 to 9 of 9

Conditional formatting forumula by date

  1. #1
    Registered User
    Join Date
    02-03-2012
    Location
    Switzerland
    MS-Off Ver
    Excel 2010
    Posts
    10

    Conditional formatting forumula by date

    Hello,

    Im trying to make an excel sheet to help manage my bills.

    my objective is to apply conditional formatting to the "Date Payable" Column. So if todays date approaches within 5 days of the Date Payable date the cell would turn red....

    I understand how to manipulate the colors etc.. I need your help in order to make a forulma to put in the conditional formatting new rule box...

    I was also wondering it is possible to use conditional formatting to format an entire row, based on the value of one cell in that row?


    Thank you very much your help, it is greatly appreciated..
    Attached Files Attached Files
    Last edited by Krazle; 04-19-2012 at 11:02 AM.

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional formatting forumula by date

    Hi

    Yes you can do it.

    Highlight all the rows that you need and in Conditional format rules, put this formula

    =$F3<=TODAY()+5

    Choose your color. OK.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

    Re: Conditional formatting forumula by date

    Hi check attachment
    I hope that is ok
    Attached Files Attached Files
    Regards

    tom1977

    If You are satisfied with my solution click the small star icon on the left to say thanks.

  4. #4
    Registered User
    Join Date
    02-03-2012
    Location
    Switzerland
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Conditional formatting forumula by date

    hahaha =) thats absolutely perfect, that you so much for your help Tom and Fotis!!!!!

  5. #5
    Registered User
    Join Date
    02-03-2012
    Location
    Switzerland
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Conditional formatting forumula by date

    hey guys, im sorry, i was happy to quickly....

    I realised if i use both your methods, this also highlights all cells that are blank... is there any way i can avoid this?

  6. #6
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

    Re: Conditional formatting forumula by date

    let see now
    Attached Files Attached Files

  7. #7
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional formatting forumula by date

    =and($f3="";$f3<=today()+5)

  8. #8
    Registered User
    Join Date
    02-03-2012
    Location
    Switzerland
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Conditional formatting forumula by date

    Dear fotis, your formula gives me an error in syntax.... i tried changing the ; to a simple ,
    this results in it highlighting all blank cells.

    Dear Tom, your doucement works perfectly, i expanded the area of the conditioning all the way to H999 instead of H24, but unfortunetly it does not highlight new entries i make...
    and i also wanted to ask if it was possible for it not to highlight dates that are older than a week from today? otherwise the entire column will just end up red at one point...

    thank you so much for your help guys, im sorry to waste your time for such useless stuff, but when i make something, i like to make it right.

  9. #9
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional formatting forumula by date

    Of course couldn't work, because was a small mistake there..

    =and($f3="",$f3<=today()+5)>>Wrong

    =and($f3<>"",$f3<=today()+5)>>Correct
    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