Conditional Formatting for Cells Containing Formulas

Kelly Pratt

I am working on a project milestone sheet and when projects are first entered the milestone dates are calculated using formulas, based on the start date. As the project gets closer, we will have users manually overriding the formula with the 'actual' date if it is changing. For this solution, we are not using column formulas, but just have them in the projects that are scheduled farther out.

We want to be able to see whether a cell currently contains a formula or if it's just a date that someone has entered. Does anyone have an idea on how that could be conditionally formatted?

I attempted to use the paramater contains an equals (=) sign, but that didn't work. Curious to hear others thoughts.

Thanks!

Mike TV

@Kelly Pratt

I do not know of any way to do that because we'd need to have a command that is basically the opposite of the VALUE function. Maybe someone else will respond later with a solution. If not, I'd suggest having the person who manually fills in the date also change the background color of that date cell so people will know it was manually entered. If they have time to manually enter a date in a field they have time to color it's background as well.

Andrée Starå

Hi @Kelly Pratt

I hope you're well and safe!

Could the date change when it's set with the formula except for the manual change?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Kelly Pratt

@Andrée Starå - Yes it could - because they could manually change one date, which could automatically change another due to the formula calculation.

I do have record date helper columns for each date so I looked into that as an option, but unfortunately, a date could be updated through the formula and not just a manual change, which wouldn't give me the accurate data needed to inform the conditional formatting.

Andrée Starå

@Kelly Pratt

I have an idea, and I'll get back to the post if it works.

Kelly Pratt

@Andrée Starå - Thank you!!

Kelly Pratt

@Andrée Starå - any luck here?

Assuming not as you didn't get back but thought I would just check!