DATE function for drop down menus

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

Find more posts tagged with

Formulas

Project Management

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…