Solved

conditional formatting in excel

Posted on 2014-01-05
6
174 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 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 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This is about my first experience with programming Arduino.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

828 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