Estimate Date Planned against the target date

✭✭

I need to calculate the planned date against the Go-Live date for each column. The dates are above the columns that should be prior to the Go-Live Target which is 10/31/22. I need to make the actual columns cells yellow for 3 dates against the planned date, red at the planned date. If a date is entered in actual column, the cell will turn green.

I am having a hard time to get this to work

Any help I will appreciate!

Rob

Thank you very much for any help!😉

Rob

• ✭✭✭✭✭

@Rob_NYC your question is a two-parter...

1. The dates need to be calculated and are simple math
2. in the [Lan Migration Complete Planned] it would be =[Go-Live Target]
3. In the [LAN Migration Scheduled Planned] it would be =[Lan Migration Complete Planned]-5
4. In [LAN Migration Ready Approved Planned] it would be =[LAN Migration Scheduled Planned]-10
5. In the [Lan Migration Ready Planned] it would be =[LAN Migration Ready Approved Planned]-13

Then you would use conditional formats in for each cell.. It is a lot of coding for the individual cells..

But you would have 3 conditional formats for each of the 4 cells so a total of 12

For each cell you would need

• if it has a date then green
• if it is the planned date using the Today() function then Red
• if the planned date is withing today(3) then yellow

Brent C. Wilson, P.Eng, PMP, Prince2

Facilityy Professional Services Inc.

http://www.facilityy.com

• ✭✭✭✭✭

@Rob_NYC your question is a two-parter...

1. The dates need to be calculated and are simple math
2. in the [Lan Migration Complete Planned] it would be =[Go-Live Target]
3. In the [LAN Migration Scheduled Planned] it would be =[Lan Migration Complete Planned]-5
4. In [LAN Migration Ready Approved Planned] it would be =[LAN Migration Scheduled Planned]-10
5. In the [Lan Migration Ready Planned] it would be =[LAN Migration Ready Approved Planned]-13

Then you would use conditional formats in for each cell.. It is a lot of coding for the individual cells..

But you would have 3 conditional formats for each of the 4 cells so a total of 12

For each cell you would need

• if it has a date then green
• if it is the planned date using the Today() function then Red
• if the planned date is withing today(3) then yellow

Brent C. Wilson, P.Eng, PMP, Prince2

Facilityy Professional Services Inc.

http://www.facilityy.com

• ✭✭

Brent

Thank you very much!

Rob

Thank you very much for any help!😉

Rob

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!