Excel date formatting help? (Office 2010)

[grebgonebad]

Hey everyone!

I am curently trying to create a spreadsheet at work which allows users to inut a date, but if that date is outside of a specified range I need it to change colour. I cannot post the specifics of this work due to legal reasons, but below is an example:

Lets say all work needs to be completed by the 01/01/2013, just for arguments sake. If Someone enters a completion date (Using the format DD/WW/YYYY) that is before this, or on this date, that cell needs to turn green. If a date is entered after this date, the cell needs to turn red. If there is no data in the cell, then the cell should be yelow.

Thanks in advance for your help!

P.S. I need this work doing today, so a quick response would be greatly appreciated!

 

[hubbardt]

You don't mention what version of Excel you are using but try these links:

http/www.mrexcel.com/articles/due-dates-with-conditional-formatting.php

http/blogs.office.com/b/microsoft-excel/archive/2013/04/04/mvp-tutorial-using-conditional-formatting-to-highlight-dates-in-excel.aspx

 

[grebgonebad]

Apologies, I should of course have mentioned that I am using Office 2010. Derp. =P I shall amend the post.

Thanks for the links! The second one looks to be most hopefull, I shall take a look now!

 

[Pinhedd]

Hey everyone!

I am curently trying to create a spreadsheet at work which allows users to inut a date, but if that date is outside of a specified range I need it to change colour. I cannot post the specifics of this work due to legal reasons, but below is an example:

Lets say all work needs to be completed by the 01/01/2013, just for arguments sake. If Someone enters a completion date (Using the format DD/WW/YYYY) that is before this, or on this date, that cell needs to turn green. If a date is entered after this date, the cell needs to turn red. If there is no data in the cell, then the cell should be yelow.

Thanks in advance for your help!

P.S. I need this work doing today, so a quick response would be greatly appreciated!

Excel has this built in. It's not immediately obvious unless you're looking for it though. It's called "Conditional Formatting" and can be found on the "Home" tab of the ribbon.

Using it is pretty straightforward.

 

[USAFRet]

Yes. Conditional Formatting is what you want. On the Home Ribbon, in Styles.

 

[grebgonebad]

In reply to both Pinhedd and USAFRet -

I am aware of the Conditional formatting function as I came accross this hile trying to figure out what to do by myself first, however, I would greatly appreciate a step by step guide on how to achieve what I am after?

Thanks guys!

 

[USAFRet]

Highlight the column in question
Click on Conditional Formatting - Highlight Cells Rules - Less Than
In the field, enter 01/01/2013
In the dropdown, select the color you want
Now...everything entered in that cell that is a date Earlier than 01/01/2013 will appear in whatever color combination you chose.

For the "no entry = Yellow":
Again, highlight the column
Conditional Formatting - New Rule
Format Only Cells that Contain...
At the bottom of that dialog - Format Only Cells with: In the dropdown, Select "Blanks"
Give it the color scheme you want.

Play around with it. It is pretty intuitive.
http/office.microsoft.com/en-us/excel-help/quick-start-apply-conditional-formatting-HA010370614.aspx

 

[grebgonebad]

Thanks! This is extremly helpful!