Conditional formatting for approaching due date/timeline

Hi Experts,

I am working on multiple projects with different start dates. I would like to create the color coding if it approaches the deadline. Sample data is given below for your reference.
 
Column A             Column B                     Column C                                                                                            Column D

Project Name      Project Start Date       First Deliverable Date                                                                       Alert
A                            01/09/2018                  Post 30 Working Days from Project Start Date      Color Green, if >15 working days are remaining, Yellow, if >7 working days are remaining and Red, if <=7 Days are remaining.
Veer SinghConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
This is not difficult using Conditional Formatting, I've found a quick tutorial, but if you you attach a small dummy file I can add the CF for you.

Applying Conditional Formatting to Dates
0
Veer SinghConsultantAuthor Commented:
thanks roy for help, I have attached the sample data file for the exact solution.
Approaching-deadline.xlsx
0
Roy CoxGroup Finance ManagerCommented:
Is this what you want?
Approaching-deadline.xlsx
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Veer SinghConsultantAuthor Commented:
Kind of, however if the deadline already passed then it is not showing red color. Can we keep it red if it crosses the deadlines. E.g. deadline is 1st September and today is 8th September then I would want the cell to be colored red.
0
Roy CoxGroup Finance ManagerCommented:
I wasn't sure about that, try this
Approaching-deadline.xlsx
0
Veer SinghConsultantAuthor Commented:
I have colored the adjacent cells to help you understand the requirement. thanks.
Approaching-deadline2.xlsx
0
Veer SinghConsultantAuthor Commented:
Please use this sheet.
Approaching-deadline2.xlsx
0
Veer SinghConsultantAuthor Commented:
and cell references need not be locked as I will have to drag the formula to 100's of cell in the same sheet. like C2,C3,C4......so on.
0
Roy CoxGroup Finance ManagerCommented:
Check the formulas they are only locked to Column C. Edit the applies to box to extend the range of the Conditional Formatting, maybe consider a Table because the formatting will expand with the Table as data is added or removed.

Overview of Excel tables
0
Veer SinghConsultantAuthor Commented:
ok. have you found the way around for coloring the cell when deadlines are crossed today(). Refer the attachment.
Approaching-deadline2.xlsx
0
Roy CoxGroup Finance ManagerCommented:
Doen't C2 meet that requirement? Due date was the seventh, today is the eighth and the cell is red.
0
Veer SinghConsultantAuthor Commented:
I did it manually to help you understand in Column E. I want it in Column D by conditional formatting. Cell D2 is not colored red when C2 due date i.e. 7th September is passed.
0
Roy CoxGroup Finance ManagerCommented:
It is in the last file I attached
Approaching-deadline.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Veer SinghConsultantAuthor Commented:
I finally got it. Thanks a lot.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.