+ Reply to Thread
Results 1 to 4 of 4

Auto fill a column based on date and color a column if past that date

  1. #1
    Registered User
    Join Date
    10-29-2013
    Location
    Utah
    MS-Off Ver
    Excel 2010
    Posts
    2

    Auto fill a column based on date and color a column if past that date

    Hello everyone,
    I've been banging my head against this this morning and I'm sure it's going to turn out to be simple. I manage all of the cell phones for our company. When someone leaves we can suspend their cell phone for 90 days without paying for it and can then re-activate it when someone else is hired. In the spreadsheet I've been using to track this I use the D column to keep track of the date that the phone was put on suspension. The phone automatically comes off of suspension after 90 days and needs to be suspended again if we still aren't using it. I would like to make something so that when I enter a date into column D, it will auto populate that cell in the E column for 90 days out. As an example, lets say I put 10/29/2013 in D2, I would like E2 to automatically populate with 1/27/2014. In addition if the current date is the date in the E column or after I would like to color the text red so I can tell at a quick glance if I need to go and re suspend a phone. I've tried doing this through conditional formatting and I haven't had luck getting it to work properly. I would like to exclude the 1st row from the formula since I use it for headers and I don't know how many phones I will have suspended at any given time. I'm using Excel 2010.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Auto fill a column based on date and color a column if past that date

    You can put this formula in E2:

    =IF(D2="","",D2+90)

    Format that cell as a date in the style you prefer, then copy down.

    To apply the conditional formatting, highlight all the cells from E2 to the bottom of your data, then click on Conditional Formatting | New Rule | Use a Formula from the bottom of the list, then put this formula in the dialogue box:

    =AND($E2<>"",$E2<=TODAY()+3)

    (Note: the +3 gives you 3 day's warning before today's date catches up with the date in E2 - you can change it to something else, or leave it out, if you wish).

    Then click on the Format button, then the Fill tab and choose the colour red. Then click OK twice to exit the CF dialogue box and Excel will automatically adjust the cell references of the selected cells.

    Hope this helps.

    Pete

    EDIT: If you have already applied conditional formatting rules to those cells, you should clear all conditional formats before applying the above.

  3. #3
    Registered User
    Join Date
    10-29-2013
    Location
    Utah
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Auto fill a column based on date and color a column if past that date

    That worked perfectly, thank you so much!

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Auto fill a column based on date and color a column if past that date

    Glad to be of help - that was quick service for your first thread !!

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Auto Fill Column with Date based on rows in other column
    By JOUIOUI in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-06-2006, 01:35 PM
  2. [SOLVED] Past date in column to change color
    By Ron de Bruin in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2005, 06:05 AM
  3. [SOLVED] Past date in column to change color
    By Ron de Bruin in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 04:05 AM
  4. [SOLVED] Past date in column to change color
    By Chris in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  5. [SOLVED] Past date in column to change color
    By Chris in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM

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