Solved

conditional formatting in excel

Posted on 2014-01-05
6
169 Views
Last Modified: 2014-01-23
For  “Number of days since certification” in cell B:1, the result should be number of days since date certified (cell B:4) minus the current DATE. As each passing day, the number should increase by 1.This result should be in cell B:1.  
For B5, it should be amber color, when 60 days before due date (B2). B5 should turn red when due date has expired.  For B5, any other days it should be green.
Can someone give me the proper programming for the CELLS mentioned. View the attachment.
EXAMPLE.xlsx
0
Comment
Question by:christoforatos1968
  • 2
  • 2
6 Comments
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39757775
See attachment.

HTH,
Dan
EXAMPLE-mod.xlsx
0
 
LVL 18

Expert Comment

by:Raheman M. Abdul
ID: 39757807
See attached file for the result.
EXAMPLE-1--Result.xlsx
0
 
LVL 18

Expert Comment

by:Raheman M. Abdul
ID: 39758017
=B2-TODAY()  is used to calculate the no of days
Based on the value in the cell, the conditional formatting is done.
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39758028
I prefer to use the function DAYS(end, start). I find it easier to read, when trying to figure out what I was trying to do.

The only gotcha in the solution is that you're allowed to apply multiple conditional formatting criteria to a single cell/range of cells.
0
 
LVL 8

Accepted Solution

by:
itjockey earned 500 total points
ID: 39761773
Hi

See attached I had use =DATEDIF(date1,date2,interval) formula,
 For color formatting I had use conditional formatting Tab  - Home - Style - Conditional formatting - Highlight cell rules - greater then 60 then Green fill
formatting - Highlight cell rules - Less     then 61 then yellow fill
formatting - Highlight cell rules - Less     then  1 then  Red    fill

Thank You
Formatting.xlsx
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Problem to With line 4 39
Copy all Sheet1-Sheets into a newly created workbook using VBA 8 33
Advice in Xamarin 21 48
Easy Excel formula needed 4 26
This is an explanation of a simple data model to help parse a JSON feed
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now