Formula to convert date (e.g., 03/13/22) to text/number field (03/13/22) in same sheet?

Lori Flanigan

A sheet that has a Dynamic View includes a date field (Start Date) that Dynamic Viewers need to use as a filter. Since users cannot filter by date fields in Dynamic View, I've added a helper column (text/number) in the sheet but cannot find a formula to pull the dates in the Start Date column into the helper column so Dynamic Viewers can use that field to filter.

Thanks for your help!

Lori F.

Jeff Reisman

@Lori Flanigan Try this:

=[Start Date]@row +""

Adding a blank text value (double quotes) to a numeric or date value converts the cell to text/number.

Lori Flanigan

Perfect! Thank you, Jeff!

Jeff Reisman

@Lori Flanigan Try this:

=[Start Date]@row +""

Adding a blank text value (double quotes) to a numeric or date value converts the cell to text/number.

Lori Flanigan

Perfect! Thank you, Jeff!

Danielle Dysinger

Hi Jeff, hi Lori, the formula =[Start Date]@row +"" didn't work for me. The column where I've entered the formula is a text/number column and returns a date and time instead of a number.

I'm trying to calculate total slack and free slack, which can then be used to calculate late start and early finish, but I can't seem to convert the date fields to a number using the formula above. Any ideas on why?

Danielle Dysinger

@Jeff Reisman @Lori Flanigan - meant to tag you in my comment above

Jeff Reisman

@Danielle Dysinger I'm unclear what you mean by "convert the date fields to a number." Can you elaborate?

Smartsheet is able to use date values in date columns to calculate other dates. For instance, if your Date column value was 04/11/23, and in another date field you entered =Date@row + 5 , you would end up with 04/16/23 as the result.