Solved

conditional formatting in excel

Posted on 2014-01-05
6
171 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 AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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…

770 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