Join or collect function changing date format

Alex GM

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.

Paul Newcome

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.

Paul Newcome

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.

Alex GM

Thank you, that was it!

Paul Newcome

Happy to help. 👍️