?
Solved

conditional formatting in excel

Posted on 2014-01-05
6
Medium Priority
?
178 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
[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
6 Comments
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39757775
See attachment.

HTH,
Dan
EXAMPLE-mod.xlsx
0
 
LVL 19

Expert Comment

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

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 35

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:
Naresh Patel earned 1500 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Progress

771 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