Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

conditional formatting in excel

Posted on 2014-01-05
Medium Priority
179 Views
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
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
• 2
• 2

LVL 35

Expert Comment

ID: 39757775
See attachment.

HTH,
Dan
EXAMPLE-mod.xlsx
0

LVL 19

Expert Comment

ID: 39757807
See attached file for the result.
EXAMPLE-1--Result.xlsx
0

LVL 19

Expert Comment

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

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

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

Question has a verified solution.

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

In this post we will learn different types of Android Layout and some basics of an Android App.
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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…
Suggested Courses
Course of the Month7 days, 20 hours left to enroll