Metrics sheet - Want to use formula to pull in date when column type is "text/number"


Hello, and thanks so much for any support with functionality or a work around.

I am creating a summary page for the system I am building. I would like the summary metrics to form a grid like table for visualizing sections of data.

First column is the title and second column is the data. The column type for pulling in the data is a text column where eI am placing different formulas to pull in different information for the summary page. Some of the formulas are index/collect, some are SUMIF and COUNTIF functions. When I try to pull in the dates, I get an error message because the column is text rather than date. I do not want the entire column to be date or the text won't pull in. It seems like I can't have both pull into the grid in a clean way because of this column type issue. It would work to have a third column just for the dates, but that throws off the visual I am going for.

See screenshots for visualization.

  1. Both circled cells have the same formula, but different column types. Note how the date pulls in on Column 4 because it is a Date Type, but not Column 2 because it is a text/number type.

Note how the date pulls in on Column 4 because it is a Date Type

and I get an error in column 2 because it is a Text Type

Formulas are the same in each cell

Is there any way to get Column 2 to populate the date, rather than having to off-set my table?

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!