Override Column Formula

wispsofsmoke

Hi,

I have a column that uses a column formula to automatically calculate a date based on other columns' criteria. However, we sometimes need to manually adjust the date and override the column formula.

Is there a way to do this?

Thanks!

Melissa Yamada

Hello @wispsofsmoke

A work around is to create a helper column (Date Override column) then on your main column with the column formula you can add an IF statement that if there is date in the "Date Override column" then return that value.

Paul Newcome

You would start it off with

=IF([Helper Column]@row <> "", [Helper Column]@row, IF(Tier@row = ……………………………………………

Melissa Yamada

Hello @wispsofsmoke

A work around is to create a helper column (Date Override column) then on your main column with the column formula you can add an IF statement that if there is date in the "Date Override column" then return that value.

wispsofsmoke

Thanks Melissa!

So, how would I add that to this formula?

=IF(Tier@row = "Tier 1 (1-3 Weeks)", [Start Date]@row + 21, IF(Tier@row = "Tier 2 (1-2 Months)", [Start Date]@row + 60, IF(Tier@row = "Tier 3 (2-3 Months)", [Start Date]@row + 90, IF(Tier@row = "Tier 4 (4-12 Months)", [Start Date]@row + 365, IF(Tier@row = "Coaching (6 Months)", [Start Date]@row + 180, "")))))

Thanks!

Paul Newcome

You would start it off with

=IF([Helper Column]@row <> "", [Helper Column]@row, IF(Tier@row = ……………………………………………

Melissa Yamada

Hello @wispsofsmoke

You can follow @Paul Newcome's formula.

You just need to add the additional IF statement at the first part of your formula to check whether the Date Override column is blank or not.

wispsofsmoke

Thanks Melissa and Paul!

Paul Newcome

Happy to help. 👍️

KimTDSYN

So why can I use an IF formula to copy from another column (need another "helper" like a hole in the head) but can't use a vlookup formula in the cell?

PS the IF doesn't work either. Seems you can't put any formula in the date field of a Gantt, if using dependencies.

Paul Newcome

@KimTDSYN You are correct that you cannot use formulas in date columns being leveraged by dependency settings. The IF formula above was not intended for that.

wispsofsmoke

Hi @Melissa Yamada and @Paul Newcome - this still isn't working properly, so I'll include more details since I might not have explained it well (or am experiencing user error :).

In the above, the Expected Completion Date should be auto-calculated based on the Start Date and Tier selected. However, we sometimes need to override and manually adjust the Expected Completion Date, which we can't do if there's a column formula.

Per what I understood from what you suggested, I put the column formula in the ECD column and treated the Expected Completion Date as the "Helper Column." However, it doesn't seem like that's right. This is the formula I put in the ECD column:

=IF([Expected Completion Date]@row <> "", [Expected Completion Date]@row, IF(Tier@row = "Tier 1 (1-3 Weeks)", [Start Date]@row + 21, IF(Tier@row = "Tier 2 (1-2 Months)", [Start Date]@row + 60, IF(Tier@row = "Tier 3 (2-3 Months)", [Start Date]@row + 90, IF(Tier@row = "Tier 4 (4-12 Months)", [Start Date]@row + 365, IF(Tier@row = "Coaching (6 Months)", [Start Date]@row + 180, ""))))))

Ultimately, I just want the auto-calculated date to appear in the Expected Completion Date column but be able to manually change it if needed.

Thanks for your help and patience!

Paul Newcome

That looks like it should be working. Is there something happening that shouldn't be?

wispsofsmoke

Hi @Paul Newcome - I still am unable to override/change the date in the ECD column and nothing shows up in the Expected Completion Date column.

So, it shows 07/01/25 in the ECD column. If I wanted to change it to, let's say, 07/15/25, I can't do that.

In ideal state, I'd hide the ECD column so only the Expected Completion Date column is visible. Then, the auto-calculated date would show in the Expected Completion Date column and I could manually change it if needed.

Thanks!

Paul Newcome

The Expected Completion Date is the manual override column. If you want to change the date output by the formula in the ECD column, simply enter the desired date in the Expected Completion Date column.

wispsofsmoke

@Paul Newcome Got it, but that's different than what I'm looking for as I only want one of the two columns (the Expected Completion Date) to be visible and I don't want to manually type a different date in a separate column - I just want to edit the existing date. Are there other options?

One thought is if the Expected Completion Date can automatically duplicate whatever is in the ECD column. That way, I could hide the ECD column and just adjust the duplicated date in the Expected Completion Date column if needed. Is there something I could add to the ECD column formula that would automatically duplicate its contents to the Expected Completion Date column?

Thanks!

Paul Newcome

The only option you would have then would be to apply the formula to every row just not as a column formula. The challenge with this would be Smartsheet's built in auto-fill for formulas requires that at least two rows immediately above and/or below the new row inserted have the formula in them. Then the formula will automatically be put into the new row without it being a column formula.

wispsofsmoke

I think that might work because we want the formula to be in every row. How would I apply the formula to every row? Thanks!