+ Reply to Thread
Results 1 to 6 of 6

Colouring cell if it has a specific date

  1. #1
    Registered User
    Join Date
    02-13-2010
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2003
    Posts
    10

    Colouring cell if it has a specific date

    Hi,

    I have a column for each month where the dates are entered on rows. Is
    it possible I can color a cell if the date entered in a cell for that
    month is 15 days past the end of the month. Like, if in a January
    column I enter a date on one of the rows as Feb.20, then the cell
    fills with red colour.

    I tried doing it using conditional formatting, but don't know how to
    say 15 days past end of the month?

    Any help will be appreciated! Thanks very much!

    Shivam
    Last edited by shivams22; 02-18-2010 at 01:13 PM.

  2. #2
    Forum Contributor marc5354's Avatar
    Join Date
    11-10-2009
    Location
    India
    MS-Off Ver
    Excel 2003 & Excel 2007
    Posts
    217

    Re: Colouring cell if it has a specific date

    Upload the file. file upload karo...

  3. #3
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    16,232

    Re: Colouring cell if it has a specific date

    It is possible to do this with conditional formatting, but for any given column how do you show what month it is? Is there a date in Row 1 that represents the month?

    For example, suppose that Row 1 has the date of the first of the month formatted as mmmm yyyy. Then the date 2/1/2010 would appear as February 2010.

    The condition you will use for A2 in that column would be a formula (not a value)

    =A2>DATE(YEAR(A$1),MONTH(A$1)+1,0)+15

    then you can use the format painter to copy this to every cell. Attached is a simple example.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    02-13-2010
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Colouring cell if it has a specific date

    Here is the attachment. dekh lo




    Quote Originally Posted by marc5354 View Post
    Upload the file. file upload karo...
    Attached Files Attached Files

  5. #5
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    16,232

    Re: Colouring cell if it has a specific date

    My solution will work for you if you type in 1/1/2010 instead of "January" (similarly for other months) and you can format it as mmmm to look the same. Then follow the instructions I gave above, modifying the cell references to match.

  6. #6
    Registered User
    Join Date
    02-13-2010
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Colouring cell if it has a specific date

    Thanks a lot!

+ 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