Date from a Cross-sheet formula not converting to a Date when row is moved #Invalid Type Error

Yvonne Stamm

I have a cross-sheet formula that adds the published date to my row. Once all the criteria are meet the row is then a workflow moves that row to the archive. But it doesn't seem to retain the Date designation once it has been moved. I was having issues with a countifs formula that was looking into the archives for items after a 12/31/24. So I finally just added a formula in the archive to convert the Published Date to Year Published. The dates that someone went back and filled in after row was moved into the Archive are working fine but the rows that had a date before the move to Archives are giving an #Invalid Data Type Error.

Paul Newcome

Exactly how are you populating the date column in the source sheet, and have you double checked that the source column is in fact set as a date type column?

Yvonne Stamm

Source columns are date column. I use the published date column in other formulas, and they work until the row gets moved to the Archive.

I have a source truth sheet (Date Column) and I then use a join(collect()) to bring in the date (Date Column) and that functions as a date until it is moved to archive.

Paul Newcome

The JOIN function converts the output into a text string.