Excel date formatting help? (Office 2010)

grebgonebad · Jul 19, 2013

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 · Jul 19, 2013

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 · Jul 19, 2013

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 · Jul 19, 2013

grebgonebad :

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 · Jul 19, 2013

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

grebgonebad · Jul 19, 2013

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 · Jul 19, 2013

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 · Jul 19, 2013

Thanks! This is extremly helpful!

Search

Excel date formatting help? (Office 2010)

grebgonebad

Distinguished

USAFRet

hubbardt

Distinguished

grebgonebad

Distinguished

Pinhedd

Distinguished

USAFRet

Illustrious

grebgonebad

Distinguished

USAFRet

Illustrious

grebgonebad

Distinguished

Similar threads

TRENDING THREADS

Share this page