Cannot convert to column formula to save my life...

Colette W.

I have two columns: Days Open and Turnaround, depending on "Status". I have tried so many ways to convert these to column formulas and Smartsheet will not let me. A lot of folks will be using this sheet so I'd rather not rely on everyone dragging down the formulas if I can help it. I've created a sheet with literally just =TODAY(), and linked that, I've tried using an automation to fill the "Date Rcv'd" column. Nothing is working….please help!

=IF(AND(Status@row <> "Complete", Status@row <> "Cancelled", Ancestors@row = 0), TODAY() - [Date Rcv'd]@row, "")

=[Linked Today]@row - Created@row

=[Linked Today]@row - [Date Rcv'd]@row

=IF(COUNT(CHILDREN()) = 0, IF(ISBLANK([Distribution Date]@row), "", [Distribution Date]@row - [Date Rcv'd]@row), "")

Colette W.

Also, I've maxed out my free ChatGPT umpteen times trying to get that to help me and just got even more frustrated.🤬

Paul Newcome

Are you getting an error message or some other output? Are you able to provide a screenshot for context?

Colette W.

The "Convert to Column Formula" option is grayed out and cannot be selected.

Paul Newcome

Are you selecting a single cell, or do you have more than one cell highlighted?

Colette W.

I'm selecting a single cell. Other column formulas in the sheet are working properly.

Paul Newcome

Are you able to provide a screenshot?

Colette W.

I've taken a prior sheet (which also had the same issue), saved as this one, so as not to mess with my data, and have tried everything I can think of to make sure that the "Date Rcv'd" column is not an issue (set it as an automation to record a date (it is a date column) when the row is created, linked it to the "official" Smartsheet-generated "created on" date. I've also tried using just =TODAY() in the formula as well as creating a reference to a helper sheet that is just for that purpose with only that data in it (second screen grab), as suggested by ChatGPT.

As you can see "Convert to Column Formula" is grayed out and not available to select. I can't do it on "Days Open" or "Turnaround" and have tried all the formulas mentioned above, plus several more, all with the same exact issue.

Paul Newcome

You shouldn't need a second sheet for a TODAY reference. That is a very obsolete method. You should be able to use the Record a Date automation to refresh the TODAY() function.

As for the formula… Do you have project dependencies turned on? If so, is the column you are trying to put the formula in the duration column? If it is, you can't use column formulas in a dependency driven column.

Colette W.

I've also tried the Record a Date automation. No, I do not have project dependencies turned on. This sheet is used to track incoming requests and their status. I want to know how many days a request has been open and then how many days it took to finish it, once it has been closed, without having to drag formulas down.

Paul Newcome

It looks like the formula is still in a dependency driven column though.

Genevieve P.

Hiya!

Just jumping in here to suggest one quick test - @Colette W. can you paste the same formula in a newly created Text/Number column, then see if it works in that new column?

If so, Paul may be right that it has to do with the column type. (We think it's a dependency column because of the "d" that's showing up, which shouldn't show up if you're using a simple formula to count days in a text column.) If you switch over to Gantt View, then select the Gear icon, this will pop-up the Project Settings for that sheet.

Let us know what you find!