Color coding cells based on due dates for a series of milestones

Thank you for assisting and looking!!!

OK, so we have a 4-month process that consists of 15 milestones, each having a specific amount of time allowed to complete. The time allowed to complete for each milestone varies per milestone (some take 3-days, some are 7 days, 5-day, 14 days, 10 days, etc).

I need a formula to show that if a milestone is completed on time, a corresponding cell turns green. If it is 1 - 4 days late it is yellow. If it is 5 or greater days late it turns red.

For example: if milestone A should take 3 days to complete and it starts on 01Sep13 and it is completed by 03Sep13 then the corresponding cell will be green.
Milestone B should take 7 days, and in a perfect world it should be completed by 10Sep13, but it is delayed and completed on 12Sep13, so the corresponding cell will be yellow.

Now here is the tricky part, Milestone C (which should take 5 days) in a perfect world should be completed by 15Sep13, but since Milestone B was delayed it is starting 2 days late. If Milestone C is completed in 5 days (started 12Sep13 and end 17Sep13), how do I show that milestone as Green, but the overall project in yellow because it is 2 days behind schedule?

We have a tracker spreadsheet where we have to put all the dates in. I need a formula that shows the current status in color for each of the 15 milestone based on the dates I enter and the allowed time for each milestone in one column AND another column that shows overall status of project in color

The intent is to track which milestones allowable time to complete (after multiple iterations of this process) are just unattainable or where allowed time can be shortened.

I hope someone can help or am I asking something that is too difficult to create and would be better off hand jamming everything in there myself?
Thanks,
Travis

Hello FDibbins, Thank you for the reply. the Colors in Column H and J are based off the values in G and I. If the value is 0 or less days, then it is green. If the value is 1 to 4 days, it is yellow. If it is greater than 5 days, then it is red.

the values in G of course will have to come from a formula that compares the difference in the dates entered, to the days the milestone is supposed to take.
The values in I will have to come from the difference in days from the Scheduled completion time to the actual completion time as a running total for the whole project.

So at the beginning of the project, I wil enter in columns A through D as the base info and schedule. then as the project moves along, I will hand jam columns E and F, so that the remaining columns G through J will automatically populate with variances and colors.

I am not Excel Savvy to this level, so any assistance is greatly appreciated.

I hope I have communicated it well enough. Thanks!!!!

Your above post helped out perfectly for column I.

for column G, that data come from the amount of days taken between columns E and F...in the case of E2 and F2 ...15 days, which when compared to column B (15 days) it was on time...so it was 0 days ahead or behind schedule, so it should be green.

What would be the formula to subtract between two dates, and then subtract it from the value in column B, so if it is zero or less than zero, then green; greater than zero but less than 5, yellow; and 5 or greater, red?

I had a question about something else, but with your help above, I figured it out thanks!

Hurray! I got everything I needed. Thank you so much FDibbins for your assistance. With your help I was able to get what I needed on the spreadsheet. I have attached my example. After numerous projects, we will be able to track trends where the allotted time is unattainable which will allwo us to move due dates to the right accordingly, or tighten due dates if they are consistently being accomplished earlier than scheduled.

So happy - thanks again.