Join or collect function changing date format
Hello all,
I've made a formula using JOIN(COLLECT()) to generate the date of completion for a certain type of task (a date entered in a different smartsheet) based on the project number for that row. The column property for both the range referenced and the column the formula is in are set as Date columns, with a DD-MMM-YYYY format for both using the calendar button in the upper right. But for some reason the formula shows the date in this format (MM/DD/YY), which leads to a NETDAYS formula giving an #INVALID DATA TYPE error.
Neither the format nor the NETDAYS formula have a problem when I manually link the cell to the specific reference I want, but that's time-consuming and increasingly impractical. If anyone has any insight I'd be very grateful.
Best Answer
-
The JOIN function converts all outputs into a text value. You will want to look into an INDEX/COLLECT instead to retain the date value.
Answers
-
The JOIN function converts all outputs into a text value. You will want to look into an INDEX/COLLECT instead to retain the date value.
-
Thank you, that was it!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!