hard formula needed

[nfsmith]

Please help, I am doing a report and need a formula that color codes cells



I have top row that had each month for 2 years that is month with last day of month ie. 3/31/16, 4/30/16, 5/31/16.....

the first column has "days" each one being different, but top working down, 156,147,139,125,105,85... (based on how long task take to complete)

I would like the interesting cell to have top row date minus the amount of days,

3/31/16 - 147 days = 11/5/15 if this date is in the future of TODAY +14 days then would be green if it is between today and 14 days would be yellow, if it is today and passed then would be red.

Today meaning every time I open it today would be reflected as current day

please help. thank you in advance

[xor]

Is it something like the attached?

[nfsmith]

yes this is what im looking for the most part, but where you have 1,2,and 3s, I would like to have the date in the color coded cells, the "date" would be the above date minus the days on the right.

Can it also be set up to change based on what day you open it?

[xor]

Originally Posted by nfsmith

yes this is what im looking for the most part, but where you have 1,2,and 3s, I would like to have the date in the color coded cells, the "date" would be the above date minus the days on the right.

See the attached.


Can it also be set up to change based on what day you open it?

It does so already. In conditional Formatting dialog you will see formulas like =(C$1-$B2)<=$A$1
and A1=TODAY()

[nfsmith]

thanks for the help!!! I still have a little trouble, getting the formula to work on the dates. I had to modify my form. I have attached the spreadsheet. the 3 front columns need to be added to gather, then subtracted from the date. then color coded.

[xor]

Is it better now?

[nfsmith]

That is perfect. thanks for the help