DATE function for drop down menus

Elias_NIO

Hi all,

I am using a drop down including different years (2019, 2020, 2021, etc.) that I need for the card view. Based on the selected drop down value in Column 1 (e.g. 2020) I want to auto-populate a ‘date’ field with the first day of the year (e.g. 2020.01.01) in column 2. I tried to use the following:

= DATE ([Column 1]@row, 1,1) 

which is not working (#INCORRECT DATA). This is, I think, due to the fact that using a drop down menu is causing the cell value to be ‘2020 and not 2020. Any way to get rid of the ‘ in the cell? Any formula that can be used here?

 

thanks, Elias

Elias_NIO

I just found out that It works if add the VALUE function so a number and not a text is being used in the DATE formula. Now it works!

Andrée Starå

Hi Elias,

Glad you solved it and thanks for sharing!

Here's some more information about the VALUE function:

https://help.smartsheet.com/function/value

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Paul Newcome

That is absolutely correct. The DATE function requires numerical values. The dropdown was populating a text string. Using the VALUE function was spot on.