Conditional Formatting For an Impact report

[Jager819]

I am trying to make an impact report that will quickly allow a customer to quickly understand the impact of set a weeks work and any delays in their schedule. What i would like to do is make a table with Column A being the project ID, Column B to BA represent a work week, and column BB be the scheduled completion date. Each week I want to be able to add or subtract day from the schedule by using inputting a number, 0= no impact Positive numbers = a delay and days added to the schedule, and Negative numbers = high productivity and subtract days form the schedule. I want to use conditional formatting to highlight green for negative numbers or 0, red for positive. This is easy enough however i would also like to use a formula to auto correct the return date based on values entered each week. I am unsure how to accomplish this any ideas? I have included a simplified example below in case i was unclear.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Project ID[/TD]
[TD]WW1[/TD]
[TD]WW2[/TD]
[TD]Return Date[/TD]
[/TR]
[TR]
[TD]Work order 1[/TD]
[TD][/TD]
[TD][/TD]
[TD]10/10/19[/TD]
[/TR]
[TR]
[TD]Work order 2 [/TD]
[TD][/TD]
[TD][/TD]
[TD]11/12/19[/TD]
[/TR]
[TR]
[TD]Work order 3[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/25/20[/TD]
[/TR]
[TR]
[TD]Work order 4[/TD]
[TD][/TD]
[TD][/TD]
[TD]2/15/20[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Maybe something like this


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Project ID​

[/td][td]

WW1​

[/td][td]

WW2​

[/td][td]

Return Date​

[/td][td]

Adjusted Date​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Work order 1​

[/td][td]

0​

[/td][td]

1​

[/td][td]

10/10/2019​

[/td][td]

10/11/2019​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Work order 2​

[/td][td]

-2​

[/td][td]

0​

[/td][td]

11/12/2019​

[/td][td]

11/10/2019​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Work order 3​

[/td][td]

0​

[/td][td]

0​

[/td][td]

01/25/2020​

[/td][td]

01/25/2020​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Work order 4​

[/td][td]

-1​

[/td][td]

-1​

[/td][td]

02/15/2020​

[/td][td]

02/13/2020​

[/td][/tr]
[/table]

Formula in E2 copied down
=D2+SUM(B2:C2)

M.

 

[Jager819]

Wow that was a lot easier that i thought it would be. Thanks it worked great, i thought there would be a conflict between date and number formats.

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

By the way, Excel stores dates as numbers being 1 equal to the 1st Jan 1900.

M.