Getting Invalid Data Type when trying to use Date() formula

Charles Osborn

Trying to convert a drop down date to Date format, so I can program some automations off the date. Keep getting Invalid Data Type. My date column below is Date format the rest are all text. Can anyone see what I am missing?

Kelly Moore

Hey @Charles Osborn

The data shown in your screenshot indicates that smartsheet views the values in your Year, Month and Day columns as text values, not numeric. In these columns wrap the formulas that are parsed from your Training Date and Time column with the VALUE() function. You will use your entire existing formula, parentheses and all, within the VALUE().

For example if the Year is currently

=RIGHT([Training Date and Time]@row,2)

Then it will become: =VALUE(RIGHT([Training Date and Time]@row,2))

You should see the values move from the left side of the column, which smartsheet formatting indicates it is text values, to the right side - reserved for numeric values. (Because of smartsheet's visualization of text vs numeric values, my personal preference is to never manually adjust the left vs right formatting in a column)

In addition to the wrapping the all three columns with the Value function, the YEAR cell needs to be a written as YYYY when used with the DATE() function.

The new formula in the Year column then becomes

=2000+VALUE(RIGHT([Training Date and Time]@row,2))

After making the above adjustments to your columns, does your DATE formula work as expected?

Kelly

Kelly Moore

Hey @Charles Osborn

The data shown in your screenshot indicates that smartsheet views the values in your Year, Month and Day columns as text values, not numeric. In these columns wrap the formulas that are parsed from your Training Date and Time column with the VALUE() function. You will use your entire existing formula, parentheses and all, within the VALUE().

For example if the Year is currently

=RIGHT([Training Date and Time]@row,2)

Then it will become: =VALUE(RIGHT([Training Date and Time]@row,2))

You should see the values move from the left side of the column, which smartsheet formatting indicates it is text values, to the right side - reserved for numeric values. (Because of smartsheet's visualization of text vs numeric values, my personal preference is to never manually adjust the left vs right formatting in a column)

In addition to the wrapping the all three columns with the Value function, the YEAR cell needs to be a written as YYYY when used with the DATE() function.

The new formula in the Year column then becomes

=2000+VALUE(RIGHT([Training Date and Time]@row,2))

After making the above adjustments to your columns, does your DATE formula work as expected?

Kelly

Charles Osborn

@Kelly Moore thank you so much. That was it. I was not aware it stored Text and numbers differently just like Excel. Thank you for the help.