Creating a prepopulating date formula that can be changed by others

camcarp

Hi All,

I am trying to create a formula that will prepopulate a date cell with a date from a previous cell. I have one column with an "Initial Completion Date" (first date expected to complete a project on) and "Expected Completion Date" (defaults to initial completion date unless project expected to be completed later).

Both are manually input, and the problem that I'm facing is that users are forgetting to fill the estimated completion date cell. However, I don't want users (besides admin) to be able to change the formula, but still be able to change the estimated completion date. Not sure if this is possible, but figured this would be the place to ask!

Thank you,

Cameron

Genevieve P.

Hi @camcarp

There currently isn't a way to have a formula in a column auto-populate with every new row added and also have users update cells (without removing the formula).

How are they entering in the information? I would suggest using a Form and making the Estimated Completion Date a required field. Or you could use Conditional Formatting to highlight the empty cells, or send an Update Request to the user.

Another option would be to have a third date column set up with an IF statement, saying If the Estimated date is Blank, return the Initial date, otherwise, return the Estimated date.

=IF([Estimated Completion Date]@row = "", [Initial Completion Date]@row, [Estimated Completion Date]@row)

This would have both dates in one column, but you would still need to have your current Estimated date showing so your users can manually fill in cells.

Let me know if any of these options will work for you!

Cheers,

Genevieve