Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel Conditional Formatting Help

Posted on 2013-01-25
5
Medium Priority
?
270 Views
Last Modified: 2013-01-25
I have a spreadsheet (see attached) for which I need to add some conditional formatting.
I would like the row to be filled in when column E (ETA_DATE) is older than today.

So, in the spreadsheet, rows 3, 7 and 10 will need to be highlighted.

Thank you for your help.
Book2.xlsx
0
Comment
Question by:Farah Mohammed
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 23

Assisted Solution

by:NBVC
NBVC earned 400 total points
ID: 38820380
Select the whole range, starting at A1 to F10,

Go to Home|Conditional Formatting|New Rule

Select to "use a formula to determine which cells to format"

enter formula:

=AND($E1<>"",$E1+0<=TODAY())

then click Format and choose from Fill tab.
0
 
LVL 24

Expert Comment

by:Steve
ID: 38820391
Column E needs to be changed to date

So use Data>Text to column>date

Then conditional format based upon formula

=E2<=Today()

Colour Yellow Fill applied

See attached.
Book2.xlsx
0
 

Author Comment

by:Farah Mohammed
ID: 38820465
I am resending my spreadsheet again. The above worked on my sample but not on the actual spreadsheet. I had dates after today highlighted, etc.

The sample I have entered manually some older dates but on this attachement is actually how the date is after running SQL.
Book2.xlsx
0
 
LVL 24

Accepted Solution

by:
Steve earned 1600 total points
ID: 38820507
Use this formula in an end column:

=DATEVALUE([@[ETA_DATE]])

Then format off that column.
Book2.xlsx
0
 

Author Closing Comment

by:Farah Mohammed
ID: 38820687
The_Barman - that worked excellent. Thank you both for your help.
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question