New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Conditional formatting Icon sets

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

Conditional formatting Icon sets

Postby kraexcel » Tue Apr 05, 2016 12:08 pm

I need to put the traffic signal lights based on the project health. One column will have the Project start date and another column will have the project phase(Phase 1, Phase 2, Phase 3). Each phase will have 3 weeks time for green signal. 1 Week additional time for yellow signal. Then Red signal.
For example
Project 1 : Phase 1 is under 3 weeks then the signal light will be green
Project 2: Phase 1 is over 3 weeks but under 4 weeks. So the signal will be yellow
Project 3: Phase 1 is over 4 weeks. The signal will be Red.

Please advise if this is possible to do using conditional formatting. or VBA coding is needed.

Thank you for your comments.

Regards,
KRA
  • 0

kraexcel
Rookie
 
Posts: 2
Joined: Apr 5, 2016
Reputation: 0
Excel Version: 2010

Re: Conditional formatting Icon sets

Postby gebobs » Tue Apr 05, 2016 2:47 pm

Sure it's possible so long as you know the start date of the phase. Post what you have so far.
  • 0

gebobs
Regular
 
Posts: 74
Joined: Feb 23, 2015
Reputation: 9
Excel Version: 2010

Re: Conditional formatting Icon sets

Postby kraexcel » Wed Apr 06, 2016 4:08 am

Hi,

Excel file is attached.
We calcuate the health from the Project start date. Phase start date is not recorded.

Thank you.
  • 0

You do not have the required permissions to view the files attached to this post.
kraexcel
Rookie
 
Posts: 2
Joined: Apr 5, 2016
Reputation: 0
Excel Version: 2010

Re: Conditional formatting Icon sets

Postby ManxXL » Mon Apr 11, 2016 9:19 am

You can do it by conditional formatting, however in your request you don’t say how long is allowed for the initial set up of a project, so I have assumed 7 days.

I’ve made a dropdown list for the Project phase, to avoid possible typo errors, and I've used a table to show the number of days from the start of the project date to give a result of G, Y, or R.

In E1 I’ve used the Today() function so the sheet will automatically update, and for ease of viewing, the number of days from the start of each project is shown in column E.

If you need to change the duration of any of the phases that can simply change the numbers in the table.
  • 0

You do not have the required permissions to view the files attached to this post.
ManxXL
Regular
 
Posts: 47
Joined: May 16, 2013
Reputation: 12
Excel Version: 2010


Return to General Excel Questions

Who is online

Users browsing this forum: No registered users and 20 guests