+ Reply to Thread
Results 1 to 5 of 5

Autopopulate date and add color formula

  1. #1
    Registered User
    Join Date
    12-01-2010
    Location
    CT
    MS-Off Ver
    Excel 2007
    Posts
    2

    Autopopulate date and add color formula

    New to this thread, but I am looking for major help, I will try to be as descriptive as possible.
    What I am looking to do is have a cell autopopulate a given date, based off the date in a previous cell. I want to add 1 year to the date. What I am also looking to be done, is that if the autopopulated date is within the present month, ie. December, the cell will show up as the color Red. If the autopopulated date is 1 months ahead of the present month, I want that cell the show up as Blue. If the autopopulated date is 2 months ahead of the present month, I want that cell the show up as Yellow. My best attempts at this so far have been the following:
    if cell A1 is 12/01/2010, have B1 as =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)).
    now this will get me to autopopulat the correct date that am looking for. But i have not been able to figure out how to color the cell based off the month.
    I do not know how to make the computer understand what today's date is and then generate a color.
    This is what i am looking to do:
    A1B1
    12/01/201012/01/2011
    01/01/201101/01/2011
    02/01/201102/01/2011

    any help on this would be much appreciated.
    Last edited by jsumislaski; 12-01-2010 at 05:25 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Autopopulate date and add color formula

    Are you looking to match the MONTH only... .or do you really mean if the date is the current month and year, etc?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    12-01-2010
    Location
    CT
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Autopopulate date and add color formula

    I am looking to add 1 year to the date that I input, and change the color of the cell based of the autopopulated month compared to the present month.

    so if cell A1 has 01/01/2008, i want cell B2 to look like 01/01/2009

    this shows that we added one year and since the month is currently december, the color shows up as blue.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Autopopulate date and add color formula

    Try this:

    Assuming the dates are in B1:B3

    Select B1:B3 and go to Home|Conditional Formatting|Manage Rules.

    Click New Rule and choose use a formula to determine which cells to format from top section, then enter formula

    =EOMONTH(B1,0)=EOMONTH(TODAY(),0)

    click Format and choose Red from Font tab.

    Then click Ok, click New Rule and repeat with formula:

    =EOMONTH(B1,0)=EOMONTH(TODAY(),1)

    choosing blue from the Font tab

    Then repeat once more with

    =EOMONTH(B1,0)=EOMONTH(TODAY(),2)

    and yellow font

  5. #5
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Autopopulate date and add color formula

    I was working on this at same time NBVC was so I'll just add my solution as an alternative:

    To arrive at your "autopopulated dates" I used:

    =EDATE(A1,12)

    in B1 and dragged down

    For the Red format:

    =MONTH(B1)=MONTH(TODAY())

    For Blue:

    =MONTH(B1)=MONTH(EDATE(TODAY(),-1))

    For Yellow:

    =MONTH(B1)=MONTH(EDATE(TODAY(),-2))

+ 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