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

1. ## 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. ## 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. ## Re: Auto fill a column based on date and color a column if past that date

That worked perfectly, thank you so much!

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

Pete

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

#### 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